Null Values in queries

[cormack15]

This has probably been discussed but I can't find the answer.

I want to query using a form. If a form field is empty I want the query to return all results from the table including null fields.

If I query 'Is Null' then it does return the null fields (indicating that the fields are, in fact bona fide null fields).

If I query Like IIf(IsNull([Forms]![Form1]![Date]),Is Null,"*12") - then, when the form field is null, it doesn't return the Is Null fields. (I've checked by substituting another expression that the Iif is returning 'True'

Can anyone help?

 

[vbaInet]

Form1 and Date are not good names, especially Date because it's an Access/VBA reserved keyword.

That aside, here's what you put in the criteria:

[Forms]![Form1]![Date] Or [Forms]![Form1]![Date] Is Null

All in one line.

 

[Taruz]

This has probably been discussed but I can't find the answer.

I want to query using a form. If a form field is empty I want the query to return all results from the table including null fields.

If I query 'Is Null' then it does return the null fields (indicating that the fields are, in fact bona fide null fields).

If I query Like IIf(IsNull([Forms]![Form1]![Date]),Is Null,"*12") - then, when the form field is null, it doesn't return the Is Null fields. (I've checked by substituting another expression that the Iif is returning 'True'

Can anyone help?

Hi..

Try this..

select field_name
from table_name
where nz(field_name,"")=IIf([Forms]![Form1]![Date] Is Null,nz(field_name,""),[Forms]![Form1]![Date])

 

[vbaInet]

Ah... I didn't notice the LIKE. So:

Like "*" & Nz([Forms]![Form1]![Date], "")

 

[cormack15]

Great - thanks guys.