Using Like & Null Values

[AndyD]

I have a query that is linked to various combo boxes.

The Query uses the Like statement to allow wildcards in the data entry

eg - ((FiatBoo.[First Name]) Like ("*" & [Forms]![FiatBooSelect]![cboFirstName] & "*"))

This works great if the record contains no Nulls.

Am I correct in saying that "Like" does not work with Nulls??

Is there a fix so that I can use Like & accept Nulls, I've tried numerous things (found on this forum) but they dont seem to help.

Hopefully someone can help?

Andy

 

[Mile-O]

((FiatBoo.[First Name]) Like ("*" & Nz([Forms]![FiatBooSelect]![cboFirstName]) & "*"))

 

[AndyD]

Thanks for your quick reply.

I tried the Nz Function but it still doesn'e seem to work.

I have one particular record that has a number of Nulls which will not display when all combo boxes are left blank! If I update the record in the table to non Null values then of course all is OK.

Doesn't the NZ function return a 0 or " " if the varible is Null?? If thats the case then query will fail as 0 or " " are not in the field? Or have I got it all wrong?

Andy

 

[Jon K]

Originally posted by AndyD
Am I correct in saying that "Like" does not work with Nulls??

The answer is Yes. The Like operator excludes null values.


You can switch the query to Design View and put the criteria for each field in a new column in the query grid like this:-

--------------------------------
Field: [First Name]=[Forms]![FiatBooSelect]![cboFirstName] OR [Forms]![FiatBooSelect]![cboFirstName] is null

Show: uncheck

Criteria: True
--------------------------------
.