troubleshooting Question

MS Access Search Query

Avatar of Tom Crowfoot
Tom CrowfootFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessSQL
13 Comments1 Solution187 ViewsLast Modified:
Dear Experts

I am building a search form to perform a simple search of peoples languages and coverage.  The way the database is constructed for this section is as follows:

3 x tables -
tblResearchContact  the main table for Peoples details
tbllang  Languages spoken, tied up to the peoples ID (done through a look-up & sub form basis)
Tblrescoverage Sectors Covered, as above

There is then a search form [frmsearch] which has 2 combo boxes in it
Combo114  this is the list of Languages
Combo105  this is the list of Sectors Covered

A simple command button brings up a list of people from a query (qryCombinedSearch)

The query queries the whole of the tblResearchContact table & places the selection from the Combo boxes to narrow the results &



This works fine if I have selected a criteria in both Combo boxes, but doesnt bring up results if one of them is left blank.

Is there a way to adjust the query to ignore a combo box if nothing is entered? For example

EG 1 & Coverage = Equities, Languages = French returns all French speaking people who cover equities

EG 2 & Coverage = Equities, Languages = (blank) returns all equities people regardless of languages spoken (including those who speak no language

Hopefully this makes sense, can anyone help
Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros