Retrieving Data from Criteria

[chippyles]

I have 6 combo boxes on a form that determine a query. Each of the fields in the query have a criteria (Like nz([forms].[tQA_Raw_Materials].[SID],"*"). I was advise to use this criteria because it would still return a value if the combo boxes were null. The only problem I am having now is that I have 2,000 records and the query returns 96 when the combo boxes are empty. What could be the problem?

Thanks!!

 

[ZmrAbdulla]

I use criteria something like this

[Forms]![myForm]![myControl]

and
like below if I want all the records to be returned if the control have no text

[Forms]![myForm]![myControl] OR [Forms]![myForm]![myControl] Is Null

Zameer Abdulla
[sub]Jack of Visual Basic Programming, Master in Dining & Sleeping[/sub]
Visit Me

 

[FancyPrairie]

Since almost all of the databases you create will require allowing the user to select the criteria they want the form filtered on or reports filtered on, you might want to consider using the function in the FAQ faq181-5497 It will create the where clause for you (without the word Where). It works for multi-select and single select list boxes, combo boxes, text boxes, ranges, check boxes, and option groups. Consequently, it should work in your case.

You only have to do 3 things to make it work.
1. Create a new module and copy and paste the functions in the FAQ into the new module.
2. Set the tag properties of your comboboxes as specified in the FAQ.
3. Call the function BuildWhere to filter your form