IIF statement in query criteria 1

[tmryan2]

I have a form with 5 text boxes called: name1, name2, name3, name4, name5.

I have a table containing a field called: name

I want to set up a query so that:
- if the text box name1 is blank, returns all records.
- if one more text boxes are filled in, returns only the specified names.

This is what I have in the query criteria for the name field:

IIF(IsNull([Forms]![formname]![name1]), [tablename]![name], [Forms]![formname]![name1] Or [Forms]![formname]![name2] Or [Forms]![formname]![name3]) Or [Forms]![formname]![name4] Or [Forms]![formname]![name5])

The query works if name1 is null, thereby returning all records. But if name1 contains text (and any of the other text boxes contain text), then it doesn't return anything.

So I don't think it is evaluating the 2nd half of the iif statement correctly. Can someone please help?

 

[PHV]

Go in the SQL view pane and amend the WHERE clause to something like this:
WHERE ...
AND ([Forms]![formname]![name1] Is Null OR [tablename].[name] In ([Forms]![formname]![name1],[Forms]![formname]![name2],[Forms]![formname]![name3],[Forms]![formname]![name4],[Forms]![formname]![name5]))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tmryan2]

Fantastic PHV! Works like a charm. Many thanks!