Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Give me ALL if is null (Access2003)

    If I am using
    If Forms!Reports!cmbAB = "AandB" And Not IsNull(Forms!Reports!cmbR) Then
    strWhere = "Region =" & Chr(34) & Forms!Reports!cmbR.Column(0) & Chr(34) & _
    " And Ofice =" & Chr(34) & Forms!Offers_Hires_Reports!cmbO.Column(0) & Chr(34)

    this gives me result for all 2 parameters.

    What if I want to leave all select boxes blank (or all except 1) and have Report run for only 1 parameter and all others to be not equal "" but be ignored and bring me ALL for these blank parameters?
    As when I was using Forms!FName!controlName or Forms!FName!controlName is null
    and is null meant I can get ALL?


    thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Give me ALL if is null (Access2003)

    Please try to explain much more clearly, precisely and completely what you mean and what you want. What are the "2 parameters" you talk about? I see 3 combo boxes mentioned in the code.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Give me ALL if is null (Access2003)

    I did it again? Sorry.

    I have different amount of combo boxes for different Reports.

    Maximum is 4 combo boxes.
    My code gets me everything using values in combo boxes, right?

    However I was asked to change that routine and let them choose 1 or 2 or 3 parameters out of 4.

    Now I need to have each parameter re-done to the condition If blank - ignore.
    Same as you suggested I did when used Parameters in query Forms!FName!ControlName or Forms!FName!ControlName is null.
    I need same affect in code. If no value - ignore.

    Some more.
    Right now if I leave combo box blank my AddWatch will give me a string
    strWhere=Region=East, City=LAX, Title=""
    Of course quesry will look for the Title="" and come empty.

    So instead I need my AddWatch to show me
    strWhere=Region=East, City=LAX
    no Title at all like if it doea not exists!

    I hope it is better explained. Thanks

  4. #4
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Give me ALL if is null (Access2003)

    OMG! It is so cool, thanks. So if one or 2 are blank it will give me ALL records?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Give me ALL if is null (Access2003)

    The code will ignore all combo boxes that are left blank by the user. It only looks at those where the user has actually selected a value. It doesn't matter how many have been left blank.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Give me ALL if is null (Access2003)

    Of course! Now I have
    If Not strWhere = "" Then
    ' Get rid of first " AND "
    strWhere = Mid(strWhere, 6)
    End If

    THANKS

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Give me ALL if is null (Access2003)

    Edited by HansV to correct error

    You need code like this:
    <code>
    Dim strWhere As String
    ' Check first combo box
    If Not IsNull(Forms!FName1!ControlName1) Then
    strWhere = strWhere & " AND Field1=" & Forms!FName1!ControlName1
    End If
    ' Check second combo box
    If Not IsNull(Forms!FName2!ControlName2) Then
    strWhere = strWhere & " AND Field2=" & Chr(34) & Forms!FName2!ControlName2 & Chr(34)
    End If
    ' Check third combo box
    If Not IsNull(Forms!FName3!ControlName3) Then
    strWhere = strWhere & " AND Field3=#" & Format(Forms!FName3!ControlName3, "mm/dd/yyyy") & "#"
    End If
    If Not strWhere = "" Then
    ' Get rid of first " AND "
    strWhere = Mid(strWhere, 6)
    End If
    ' Open report
    DoCmd.OpenReport ReportName:="rptMyReport", View:=acViewPreview, WhereCondition:=strWhere
    </code>
    In the above example, Field1 is a number or Yes/No field, Field2 is a text field and Field3 is a date field. The example can easily be modified for different field types and expanded to accomodate more conditions.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Give me ALL if is null (Access2003)

    In fact, there was an error in my reply <post:=714,151>post 714,151</post:> higher up in this thread. Sorry about that. I have edited it to correct the error.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Give me ALL if is null (Access2003)

    My neverending story never ends:-))

    I want to make sure that if my MAIN Report contains 4 to 10 SUB Reports (in the same Group Header of the MAIN)...
    should I enter the code for each Report or just for Report Open event of the MAIN Report.

    I think if all of the Reports linked to the MAIN by the same field - I maybe don't have to have code for each? Or do I?

    Thanks

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Give me ALL if is null (Access2003)

    What code? The code we've been discussing in this thread is used on a form (to open a report), as far as I can tell.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Give me ALL if is null (Access2003)

    Absolutely.
    And it says
    DoCmd.OpenReport ReportName:="rptMyReport", View:=acViewPreview, WhereCondition:=strWhere

    So when rptMyReport pops open there are few SUBS are stuck inside.
    Will those SUBs respond to the Main Report's parameters?

    As I had created it now each SUB has the same code per Report on Report Open event without line DoCmd.OpenReport ReportName:="rptMyReport", View:=acViewPreview, WhereCondition:=strWhere

    So this way each Report takes same parameters upon itself as Main Report does.

    Your code seems like it might not nessesary to add code to SUBS IF SUBS are linked to a MAIN by parameter fields. No?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Give me ALL if is null (Access2003)

    I obviously don't know the design of your report and subreports, so I can only give a general answer: if the subreports have been properly linked to the main report, they will automatically heed the filtering of the main report.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •