multiple criteria for query using combo boxes

[arizona]

I have a form with 4 combo boxes that are the criteria for a query.
I had seen where someone had used the nz function to turn a null value into a nil string and pass it back to the query if no value was selected for some of the combo boxes. It was something like this:
iif(nz([forms]![frmGeneratorSearch]![combo2], ""),
[Manufacture],[forms]![frmgeneratorsearch]![combo2])

This is just for one of the combo boxes, but I don't have the syntax correct.

Can anyony help?

Thanks

 

[dynamictiger]

(nz([forms]![frmGeneratorSearch]![combo2], "")

Will give you a criteria of "" or combo2. Isn't this what you want.

 

[arizona]

The way I had it returns all the records that have a value in the manufacture field.
The method you suggested returns nothing.
I know its close but I can't seem to get it yet.
Any other suggestions?
Thanks for your response.

 

[dynamictiger]

That's because criteria are not empty strings if they are not present. Try this:

(nz([forms]![frmGeneratorSearch]![combo2], IsNull())

or a derivative of it.

 

[Jon K]

Try referencing the combo box using:-

iif(isnull([forms]![frmgeneratorsearch]![combo2]),[Manufacture]=[Manufacture] or isnull([Manufacture]),[Manufacture]=[forms]![frmgeneratorsearch]![combo2])


It is not to be put in the Criteria cell in the query grid. Switch to SQL View and directly type it in the Where Clause of the SQL Statement like this:-

SELECT ...
FROM [TableName]
WHERE iif(isnull([forms]![frmgeneratorsearch]![combo2]),[Manufacture]=[Manufacture] or isnull([Manufacture]),[Manufacture]=[forms]![frmgeneratorsearch]![combo2])
AND ... etc for other combo boxes


Hope it helps.

edited: added the two ) in the expression which were left out when first posted.

 

[arizona]

This is what I was after and it works

IIf(Trim(nz([forms]![frmGeneratorSearch]![combo2].[value],""))="",[kw],[forms]![frmgeneratorsearch]![combo2].[value])

Thanks for all the help...

 

[Jon K]

Your solution works when all the four combo boxes are selected.

If you leave some of the combo boxes blank and the table fields referenced by these blank combo boxes happen to contain some Null values, it will not work.

In your example, if [forms]![frmGeneratorSearch]![combo2] is left blank (i.e. Null), ""="" is True, so the criteria becomes [kw]=[kw], which will return every record EXCEPT those records where [kw] is null.

 

[arizona]

Jon,

It is working with the 4 different combo boxes, I get the right records, but you are right about if any of the fields contain a null value. Then those records are not returned. Any ideas on how to get around that?

 

[Jon K]

Try this setting in a column in the query grid:-

Field: IIf(IsNull([forms]![frmGeneratorSearch]![combo2]), [[kw]=[kw] or IsNull([kw]), [kw]=[forms]![frmGeneratorSearch]![combo2])

Criteria: <>False

 

[arizona]

Jon,

I tried that and it seems to work good.

Thank you for your time and help!

 

[sbgirling]

I know that this is a really old thread, but wondering if someone can explain what the "kw" is in the notes above. It seems to magically appear and I'm not sure I've seen it before. I'm attempting to do something very similar and can't believe there isn't a way to just put something in the criteria grid to do this...