Multiple Search Boxes

[Hamm]

Hi Guys

I have set up a query to search my table based on a number of different search boxes. ie each column has a search box and the idea is to filter the table by any combination of the search boxes.
The searching appears to be working except it is not showing records with blanks in any of the searchable columns.

I tried using:

Nz([forms]![Home Screen]![SrchTextNumber],"")=""

but obviously this only works when I enter something in the SrchTextNumber search box. I want to be able to create a statement that basically says if all the search boxes are empty then show all records.

Does anyone have any idea of how i can get this done?

Thanks

Here is My SQL for reference:

SELECT Assets.[Job Number], Assets.[Project Name], Assets.Size, Assets.[Service Line], Assets.[Assignment Type], Assets.Client, Assets.[Property Name], Assets.City, Jobs.Country, Assets.[Project Leader], Jobs.[Date Billed], Assets.[Job ID], Assets.[Archive Location]
FROM Jobs INNER JOIN Assets ON Jobs.ID = Assets.[Job ID]
WHERE (((Assets.[Job Number]) Like "*" & [forms]![Home Screen]![SrchTextNumber] & "*") AND ((Assets.[Project Name]) Like "*" & [forms]![Home Screen]![SrchTextProject] & "*") AND ((Assets.Size) Like "*" & [forms]![Home Screen]![SrchTextSize] & "*") AND ((Assets.[Service Line]) Like "*" & [forms]![Home Screen]![SrchTextService] & "*") AND ((Assets.[Assignment Type]) Like "*" & [forms]![Home Screen]![SrchTextAssignment] & "*") AND ((Assets.Client) Like "*" & [forms]![Home Screen]![SrchTextClient] & "*") AND ((Assets.[Property Name]) Like "*" & [forms]![Home Screen]![SrchTextAsset] & "*") AND ((Assets.City) Like "*" & [forms]![Home Screen]![SrchTextCity] & "*") AND ((Jobs.Country) Like "*" & [forms]![Home Screen]![SrchTextCountry] & "*") AND ((Jobs.[Date Billed]) Like "*" & [forms]![Home Screen]![SrchTextDate] & "*")) OR ((Nz([forms]![Home Screen]![SrchTextNumber],"")=""));

[June7]

If fields are allowed to be empty, ie, Null, then need to handle the null. Create field with expression using the Nz function and apply the wildcard filter criteria to that constructed field.

[Hamm]

Hi June7

Can you please expand? How exactly should the Nz expression read? and do I simply use * as an OR in my query or an AND?

thanks
Hamm

[orange]

There is a video tutorial here that may help
http://www.datapigtechnologies.com/f...earchform.html

[Hamm]

Thanks Orange but i have already achieved that. My issue is with records which have blank fields under something i am searching for. The query doesn't show any of these records.

[Hamm]

Thanks guys, i have now got it working. My fields needed to be expressions based on the Nz function to change Null Values to "" rather than straight fields from the table

[orange]

Glad you have it working.