Query won't read "Is Not Null" (1 Viewer)

[slyvsspy]

I have built a query named "multipleselectquery" that reads the criteria information from an unbound control box on a form named "multipleselectform". 1 of two values is sent to the query: Is Null ; or Is Not Null. The query will not read these values from the form but it will if I put them in manually. So if anyone can help me figure out what I'm doing wrong I would really appreciate it.

 

[Paul_Bricker]

On the criteria line try
=Forms!multipleselectform!ControlNameHere Or Forms!multipleselectform!ControlNameHere Is Null

I think that's the syntax you want. Of there's a problem, take out the space between Is Null and make it IsNull. But I think it's Is Null.

Paul

 

[Pat Hartman]

The ONLY type of parameter you can use in a query is one that substitutes a value for a column name. You cannot use parameters to provide relational operators or the number you want for the TOP values predicate or a table name, etc.

What you have done will look like the following to Jet:

Where SomeField = "Is Not Null";

This is clearly not what you intend. If Paul's solution will not help you, you'll either need to create two queries and choose which to run or build the SQL on the fly using VBA.

 

[Paul_Bricker]

My apologies for not putting my reading glasses on before I read your question. The expression I posted will not differenciate between Is Null and Is Not Null.
Are you trying to do this because the Form won't display a list of values for any fields when you use Filter By Form except Is Null and Is Not Null?
If that's the case, you can go to Tools....Options....Edit/Find.... and look at the box that says
"Don't display lists where more than this number of records read"

and then you'll have a number like 1000.

If you increase that number to a value greater than the number of records you have in your Form, you will see lists of values when you use Filter By Form instead of Is Null and Is Not Null. Don't know if this will help, but if it's what you are looking for then my original post should help.

Hi Pat, hope everything is well with you.
Paul

 

[slyvsspy]

Thanks

Thanks for all the help Paul!!!