query criteria (2002)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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

Dave

Reply | Quote