Problem with Like "*" not returning Nulls

[SmokeEater]

I am running a query with Like "*" in the criteria and the query returns everything except the records with a null value in that field. Am I missing something? I thought that Like "*" should return everything.

 

[PHV]

Like '*' returns everything BUT NULL.
Consider NULL as UNKNOWN: soemething unknown can't be like anything, do you follow me ?

If you want everything then simply don't put any criteria.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SmokeEater]

Yes, I do. I have failed to supply enogh information for you to understand my problem. I have built a query that gets its criteria from a form using

Like [Forms]![Report Date - 99]![text16]

The Control Source property of text16 looks like this

=IIf(IsNull([Combo8]),"*",[Combo8])

This combination has been used in older versions of Access with no problems. I am using Access 2000.

 

[PHV]

Replace this:
Like [Forms]![Report Date - 99]![text16]
with this:
Like [Forms]![Report Date - 99]![text16] OR [Forms]![Report Date - 99]![text16] Is Null

And this:
=IIf(IsNull([Combo8]),"*",[Combo8])
with this:
=[Combo8]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

You can also concatenate a zero-length-string to your field name

WHERE [FieldName] & "" Like [Forms]![Report Date - 99]![text16]

BTW: do yourself a favor and name your text boxes. [text16] doesn't provide a clue regarding its purpose.

Duane
Hook'D on Access
MS Access MVP