Query for null-value in numeric field

[WimDC]

Hi,

One of the fields in my table is numeric and can have no value or a numeric value.
I also have a form where users can enter the criterium OR can leave that criterium-field empty. (query by form)

In case they enter a numeric value, my query retrieves all the matching records. So far so good.
But when they leave that criterium-field empty, I get no records. BUT I WANT ALL THE RECORDS, those with a numeric value but also those without a value.

With a text-field, I use the like "*" & forms!critform!critfield & "*" but this doesn't work with numeric fields.
When I test my query with the criterium Is Null or >0 I get all the records. So I know this works too.
But when I try the criterium Nz(forms!critform!critfield;Is Null or >0) I don't get any record. Changing it to Nz(forms!critform!critfield;"Is Null or >0") gives me an error.

How can I get the Is Null or >0 as criterium if the criteriumfield is left empty?

Thanks for helping!

Wim

I attached a simplified database with some records I use to test (my other database I'm using contains confidential data).
Some field-types are in Dutch (but I added some explanation in English...)

 

[Brianwarnock]

Don't use Like for searches unless you want partial matches.
Like criteria will not return Nulls, mind you I thought numeric fields defaulted to 0 and could not be null.

However to answer your question the criteria should be in SQL view

Field = Forms!critform!critfield or forms!critform!critfield is null

Brian

 

[Pat Hartman]

I thought numeric fields defaulted to 0 and could not be null

Numeric fields can contain any numeric value or Null unless constrained by validation rules or RI. Text fields may contain any character value, Null, or a ZLS unless constrained by validation rules or RI. Numeric fields can be set to default to Null or a numeric value. Text fields can be set to default to any character value, Null, or ZLS.

 

[WimDC]

Hi Brian,

thanks for your suggestion. It seems to work for all values, even "null"!
So, that's what I wanted and where I'm happy for

But can you just explain how to understand that query? (I want to learn...)

The first part (= Forms!critform!critfield) is easy (if the value in my criterium-form is 14, the query gives me all the records with the appropriate field = 14).

But what does "forms!critform!critfield is null" mean? Is this a boolean result?
Access "converts" your SQL-criteria as an extra field-column with the expression Expr1:forms!critform!critfield and the criterium "Is Null".
I just can't understand WHY it works...

Wim

 

[Pat Hartman]

The second part of the expression returns true if the user left the selection criteria empty. This is a very useful technique when you need to provide for optional criteria especially if more than one field is involved.

Where (Field1 = Forms!yourform!Field1 OR Forms!yourform!Field1 Is Null)
AND (Field2 = Forms!yourform!Field2 OR Forms!yourform!Field2 Is Null)
AND (Field3 = Forms!yourform!Field3 OR Forms!yourform!Field3 Is Null)
...

Note that when multiple conditions are required, the parentheses are REQUIRED to control the proper evaluation of the AND and OR operators.

 

[Brianwarnock]

Thank you Pat , I hadn't been back since my original post.

Brian