Like & Null troubles in a query

[ScubaStevo]

I have a query that searches on all fields from a table. I use the Like command to search on each field from a number of textboxes in a form like this:

Like ("*" & [Forms]![QuoteSearch]![CustomerNumber] & "*")

This works fine for all records except for those that are NULL. What is the best approach to rectify this situation? Should I include NULL in each search or make the table have a default value for each field so each field wont be NULL??

 

[PHV]

Instead of
WHERE ... AND [table name].[fieldname] Like ("*" & [Forms]![QuoteSearch]![CustomerNumber] & "*") ...
You may try this:
WHERE ... AND Nz([table name].[fieldname]) Like "*" & [Forms]![QuoteSearch]![CustomerNumber] & "*" ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

I use
WHERE ... AND [table name].[fieldname] & "" Like "*" & [Forms]![QuoteSearch]![CustomerNumber] & "*" ...

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[PHV]

Yes, work the same way, but one more character to type

 

[ScubaStevo]

Excellent thanks wery much