Not all results showing in query

[lewis1682]

Okay, so I have 2 tables - Contacts, Category. In the contacts table there is lookup value to the category table. I have a query called Search_Contacts below is the SQL code:

SELECT Contacts.ID, Contacts.Title, Contacts.FirstName, Contacts.LastName, Contacts.JobTitle, Contacts.Company, Contacts.AddressLine1, Contacts.AddressLine2, Contacts.Town, Contacts.County, Contacts.PostalCode, Contacts.Phone, Contacts.Fax, Contacts.Email, Contacts.Website, Contacts.Category, Contacts.Include, Contacts.Notes, Contacts.Newsletter, Contacts.AGMFROM Contacts
WHERE (((Contacts.FirstName) Like "*" & [forms]![Filter_Contacts]![s_FirstName] & "*") AND ((Contacts.LastName) Like "*" & [forms]![Filter_Contacts]![s_LastName] & "*") AND ((Contacts.Company) Like "*" & [forms]![Filter_Contacts]![s_Company] & "*") AND ((Contacts.County) Like "*" & [forms]![Filter_Contacts]![s_County] & "*") AND ((Contacts.PostalCode) Like "*" & [forms]![Filter_Contacts]![s_PostalCode] & "*") AND ((Contacts.Category) Like "*" & [forms]![Filter_Contacts]![CategoryList] & "*"));

If not all data fields in the table is not filled in then the query doesn't return results please help

[June7]

The wildcard method only works if every record has a string value. Need to handle nulls. Options:

1. use Or Is Null as criteria under each field, like
(((Contacts.FirstName) Like "*" & [forms]![Filter_Contacts]![s_FirstName] & "*" Or Is Null)

2. calculate alias fields to handle possible null and apply criteria to the alias field, can set these fields to not display or don't pull the native fields into query
[FirstName] & "" AS FN

3. use VBA procedure to construct filter criteria string and pass it in the WHERE CONDITION argument of OpenForm (or OpenReport) method

[lewis1682]

I believe the 3rd method would work great, but how would I get this to do it from the textboxes and one combobox? Then it needs to show the results as a forms data where the user will cycle through the results.

Thank you for your help.

[June7]

Review: http://allenbrowne.com/ser-62.html