Create a Multi-Parameter Search

Question

I'm trying to build a form that will allow users to search my database by multiple fields (company name, first name, last name, city, state, zip, country, or email).  The problem is that not all records have data in all those fields.  My query (SQL below) does not return records if any of the fields being asked for in the form is null in the record.  For instance, even though we have several Belgian companies, it returns no records if I simply put "Belgium" in the country field on the form because we have no state or zip code for them.  Is there a way to make this work the way I want it to, or am I going to have to break it up into separate searches?

Here is my query as written:

SELECT [Main Prospect Query Including Inactives].exh_masid, [Main Prospect Query Including Inactives].exh_csort, [Main Prospect Query Including Inactives].[Company Name], [tblExhibitorContacts]![FirstName] & " " & [tblExhibitorContacts]![LastName] AS Contact, tblExhibitorContacts.Address1, [tblExhibitorContacts]![City] & ", " & [tblExhibitorContacts]![StateProvince] & " " & [tblExhibitorContacts]![Country] AS Location, qryMostRecentShow.MaxOftex_showid

FROM ([Main Prospect Query Including Inactives] LEFT JOIN qryMostRecentShow ON [Main Prospect Query Including Inactives].exh_masid = qryMostRecentShow.tex_masid) INNER JOIN tblExhibitorContacts ON [Main Prospect Query Including Inactives].exh_masid = tblExhibitorContacts.exh_masid

WHERE ((([Main Prospect Query Including Inactives].[Company Name]) Like "*" & [Forms]![frmProspectAdvanced]![Company Name] & "*") AND ((tblExhibitorContacts.FirstName) Like "*" & [Forms]![frmProspectAdvanced]![exh_first] & "*") AND ((tblExhibitorContacts.LastName) Like "*" & [Forms]![frmProspectAdvanced]![exh_last] & "*") AND ((tblExhibitorContacts.City) Like "*" & [Forms]![frmProspectAdvanced]![exh_city] & "*") AND ((tblExhibitorContacts.StateProvince) Like "*" & [Forms]![frmProspectAdvanced]![exh_stat] & "*") AND ((tblExhibitorContacts.Zip) Like "*" & [Forms]![frmProspectAdvanced]![exh_zip] & "*") AND ((tblExhibitorContacts.Country) Like "*" & [Forms]![frmProspectAdvanced]![exh_cntr] & "*") AND ((tblExhibitorContacts.email) Like "*" & [Forms]![frmProspectAdvanced]![exh_email] & "*"))

ORDER BY [Main Prospect Query Including Inactives].exh_csort, [Main Prospect Query Including Inactives].[Company Name];

Answer 1

Have you seen this? http://www.allenbrowne.com/ser-62.html

Whenever I have to do such a search I will use multiple controls to create a filter. I will then use that filter to open a form or report with the matching records.

Answer 2

Use the NZ function ---

WHERE (((Nz([Main Prospect Query Including Inactives].[Company Name],"")) Like "*" & [Forms]![frmProspectAdvanced]![Company Name] & "*") AND ((Nz(tblExhibitorContacts.FirstName, "")) Like "*" & [Forms]![frmProspectAdvanced]![exh_first] & "*") AND .......

Answer 3

Hi,

Where are you using the query? The problem is the use of the AND operator. Typically, you can use a search form by dynamically building the criteria. That way, you can eliminate fields that the users didn't want to filter.

Just my 2 cents...