Search Query problem with nulls

[techtony]

I have a customer table has a query that depends on a search form that asks for a phone number.




There are four phone number fields - Home, Cell, Work and Other

There will be at least 1 number in at least ONE of those fields.

The problem is when you search for a customer by telephone number. I want to enter the phone number in a search field in a search form. If I enter a phone number in the search form, and one of the phone numbers are NULL, then the record is ignored and not displayed.

Here is the query I am using:

SELECT
Customers.customer_id,
Customers.first_name,
Customers.last_name,
Customers.company_name,
Customers.address_1,
Customers.city,
Customers.zipcode,
Customers.home_phone,
Customers.cell_phone,
Customers.work_phone,
Customers.other_phone
FROM
Customers
WHERE
(((Customers.first_name) Like "*" & [Forms]![Customer Search Form]![fname])
AND
((Customers.last_name) Like "*" & [Forms]![Customer Search Form]![lname] & "*")
AND
((Customers.home_phone) Like "*" & [Forms]![Customer Search Form]![phone] & "*")
AND
((Customers.cell_phone) Like "*" & [Forms]![Customer Search Form]![phone] & "*")
AND
((Customers.work_phone) Like "*" & [Forms]![Customer Search Form]![phone] & "*")
AND
((Customers.other_phone) Like "*" & [Forms]![Customer Search Form]![phone] & "*"));

I have been reading up on Null values, but everything seems to either exclude all records that contain NULL or include all records that contain NULL. I want to simply find a match for the phone number on any of these telephone related fields.

Any suggestions?

[ItsMe]

Perhaps your issue is the AND operator

Perhaps the OR operator better suits your needs.

[techtony]

no, not the AND vs OR - this is a null problem. Does anyone know how to handle nulls in this situation?

[June7]

One way:

AND
((Customers.cell_phone) Like "*" & [Forms]![Customer Search Form]![phone] & "*" Or Is Null)
AND

Another is to convert the nulls to a string by expression in query and apply the filter parameter to the constructed field:

(Nz(Customers.cell_phone,"") Like "*" & [Forms]![Customer Search Form]![phone] & "*")