User Input or Null Value in Same Query

[shaggy]

How can I return records based on user input for a field and if there is no user input (blank) return all records with null values in that same field.

I can do it with two separate queries. One using "like" and the other using "is null" but I would like to combine them into one query.

 

[Jon K]

Type in the SQL View of a new query, replacing with the correct field names and table name:-

SELECT Field1, Field2, ... , Fieldx
FROM TableName
WHERE iif([Enter value] is null, [FieldName] is null, [FieldName]=[Enter value])


Run the query.

 

[Pat Hartman]

Select ...
From YourTable Where YourField = Forms!YourForm!YourField OR IsNull(Forms!YourForm!YourField);

Jon K,
You cannot use the IIf() function the way you are suggesting. It is NOT possible to change a where clause within a query using the IIf() function. You can build a custom where clause with VBA but the IIf() as you have coded it will not work.

EDIT: added closing paren

 

[Jon K]

Pat,
I find the IIF() function works in the query (see attached file).

Your using of OR is simpler and is generally better than using IIF().

However when there is no user input, your "OR IsNull(Forms!YourForm!YourField)" will evaluate to True and thus return every record in the table. But it is not what is intended by shaggy, who wrote "if there is no user input (blank) return all records with null values". Jon K

 

[Pat Hartman]

Jon, the sample you posted returns only rows with null in Area when the parameter is left empty. That is not what shaggy wants. He wants ALL rows returned when the criteria is left empty.