Parameter queries and null values

[JeffN]

I have written a parameter query that allows a user to select the criteria Y,
N or C when the query runs. I have now been asked to modify the query so
that they can also look for null values. The way they envisiged it working
is that they could type Y, N, C or leave it blank to look for null values.

Does anyone know how to do this?

Thanks for your help.

 

[Jerry Whittle]

Something like below:

WHERE ASIF.TABLE_NAME Like [Enter Date or Leave Blank for All]
Or ASIF.TABLE_NAME Is Null;

 

[John Spencer]

WHERE SomeTable.SomeField = [Enter Y, N, or C Leave Blank for All] Or
[Enter Y, N, or C Leave Blank for All] is Null

Or if you want to find just the nulls, then this should work

WHERE SomeTable.SomeField = [Enter Y, N, or C Leave Blank for Null] OR
([Enter Y, N, or C Leave Blank for Null] is Null AND SomeTable.SomeField is Null)

OR one other option (that can be slow with very large sets of data)

WHERE SomeTable.Somefield & "" = NZ([Enter Y, N, or C Leave Blank for All],"")


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County