Hello,
I have a problem, I have a query with a criteria for Field1: [Forms]![fTestForm]![txtNumber]
This basically shows me all records with that txtNumber. My dilema is that this query looks at many textboxes as filters and in this case txtNumber might be left blank. Whenever I leave it blank it does not display any records and it should. Is there a way to show records with empty Field1when txtNumber is not selected but if a number was inputed it would display records for the number.

THanks

Let me know if I am confusing you.

Let me rephrase it:
here is my criteria so far,
IIf(Nz([Forms]![fLAMPREditProd]![txtInt2ID],0)=0,Null,[Forms]![fLAMPREditProd]![txtInt2ID])

The False part of it works but whenever the txtInt2ID textbox is empty then the True part does not display Null records. It does not let me put "Is Null" because its a Number field, any idea on the error or a solution to it?
Thanks

Actually, your query is working correctly... as Access understands it. If there is nothing there in the search textbox, then it is Null. The number of records where the field corresponding to that textbox is null is equal to zero. If you always fill this field with a number, then getting no records back IS CORRECT!

But you want, instead, for Access to show ALL records if your search textbox is null, no? Then change NULL to Not Null (no quotes). Your criteria would look like this:

= Nz([Forms]![fLAMPREditProd]![txtInt2ID],Not Null)

If txtInt2ID has a value, the query looks up the value. If txtInt2ID is null (no value), the Access will return all records where that field is not null.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

not sure why but its not working

Greg,

The part of NZ() (above) returned when the first argument is [u]not</u> Null will return a string, right?
So shouldn’t the part returned if the first argument [u]is</u> Null be a string also ("Not Null")?

That is correct.
No, that is not correct. If you encase the words with the quotation marks you turn the condition of NOT NULL into a string of "Not Null". You're not looking for a string, you're looking for the condition of the the field having a value. Think of it this way.

5 is a byte, value 5
"5" is a one-character string, value to "5"

True is a boolean, value TRUE or -1.
"True" is a four-character string, value "True"

Null is a reserved Access variable/function denoting no value, value... nothing at all!
"Null" is a four-character string, value "Null"

Not Null is a combination of two reserved words. The Not negates the second part of the condition. Therefore Not Null means to have a value (you don't care what).
"Not Null" is an eight-character string, value "Not Null".

See what I mean? If you put the quotes, your query will actually look for the string "Not Null" in your field rather than search for any value that is not null.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division