How to query when control param is null?

  • Thread starter Thread starter David Portwood
  • Start date Start date
D

David Portwood

I want to run a query with criteria based on a form control. Works fine for
nonblank values in the control but when the control is null the query finds
no records, even though records do exist with null values in that field.

Does anybody know how I can fix this?
 
You cannot use a parameter to find Null. By its nature, Null is not equal to
Null (i.e.: Null = Null will never return True)

If the parameter is Null, do you want only Null values returned, or do you
want all the rows returned?

If the former, your SQL should be something like

WHERE MyField = Forms!MyForm!MyControl OR (MyField IS NULL AND
Forms!MyForm!MyControl IS NULL)

If the latter, your SQL should be something like

WHERE MyField = Forms!MyForm!MyControl OR Forms!MyForm!MyControl IS NULL
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Parameter Value error in a Subform 0
Is Null 2
NOT function not working - can prove with screenshots 1
Parameter Query With Null Values 3
Finding null 4
Return ALL records with NZ function 4
Query based on Check Box 1
Query Parameter using multiple options 0

Back
Top