Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    Eastern Pennsylvania, Pennsylvania, USA
    Posts
    97
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query criteria (2002)

    Hi,
    I have 5 combo boxes(cmbFilter1, cmbFilter2,etc) on a form (frmListBuild). I want to build a query as a recordsource for a report and enter criteria to match one or more of the combo box choices. The user may not use every combo box for a report, so this may be a null value issue. I am getting inconsistent results when entering this string - Like Nz([Forms]![frmListBuild]![cmbFilter1],"*") - in the field 1 criteria top row, - Like Nz([Forms]![frmListBuild]![cmbFilter2],"*") - in the field 2 criteria top row, etc.
    It seems like a simple idea, but I must be missing something.
    Any help would be greatly appreciated. Thanx

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

    Re: query criteria (2002)

    Are the fields you want to filter all text fields? In what sense are you getting inconsistent results?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: query criteria (2002)

    Dave

    I sometimes do two or three combo filters gizmos on my forms.

    The primary combo runs off a look up table, the secondary and/or tertiary run of a query where the values of the combos are criteria for a query that a subform on the form runs off.

    On the update event of each combo I Me.Requery, Me.Refresh to update the values in the other combos... would this work for your form?
    Jerry

  4. #4
    Star Lounger
    Join Date
    Mar 2001
    Location
    Eastern Pennsylvania, Pennsylvania, USA
    Posts
    97
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query criteria (2002)

    Thanks for the response.
    All of the fields are text, as well as the stored combo values. The inconsistent results occur if (for instance) only filter 1 and 3 are used. The form filtering is not a problem. I am trying to build a query referencing the combo box values as criteria, to use behind a report. I'm attaching a screen shot of the query as I have it now.

    Thanx Again,
    Dave

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

    Re: query criteria (2002)

    Something similar works OK for me in a quick test. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: query criteria (2002)

    Hi Dave

    Same way as I do it apart from I normally don't have the nz() function.

    Questions:

    1) Did you use the Expression Builder?
    2) Or did you hand code it, and if so
    3) Have you checked for typo's on frmListBuild

    otherwise, as Hans suggested, could you send a cut down version of the db, with secure info XXX'd out. I will pop in once in a while but Hans is the Guru for this type of problem. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

  7. #7
    Star Lounger
    Join Date
    Mar 2001
    Location
    Eastern Pennsylvania, Pennsylvania, USA
    Posts
    97
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query criteria (2002)

    Dear Jezza and HansV,
    While scaling down the db so I could post it I found that if a record has a null value in a field being criteriad (?!) by a value in a combo box, the record is not returned. With the NZ() I was trying to force 'if null then return all (*)'. I guess it doesn't work. Let me know if there's a solution you may have. worst case I'll create a vb recordset behind the report.

    Thanx for the replies...

    Dave

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

    Re: query criteria (2002)

    Try the following:
    - Remove the criteria referring to the combo boxes.
    - Add a column [Forms]![frmListBuild]![cmbFilter1]
    - Clear the Show check box for this column.
    - Enter the following expression in the top criteria line for this column:

    [City] Or Is Null

    - Repeat for the other combo boxes (with the appropriate combo box names and field names)

  9. #9
    Star Lounger
    Join Date
    Mar 2001
    Location
    Eastern Pennsylvania, Pennsylvania, USA
    Posts
    97
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query criteria (2002)

    Hans,
    Thanks for the suggestion. It solved the problem. Your expertise is greatly appreciated...

    Dave

Posting Permissions

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