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 &

tbllang
tbllanguagecoverage
[Forms]![frmsearch]![Combo114]

Tblrescoverage
tblContactsCoverage
[Forms]![frmsearch]![Combo105]

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
ASKER CERTIFIED SOLUTION
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