We help IT Professionals succeed at work.
Get Started

MS Access Search Query

185 Views
Last Modified: 2013-11-28
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
Comment
Watch Question
CERTIFIED EXPERT
Top Expert 2009
Commented:
This problem has been solved!
Unlock 1 Answer and 13 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE