Multiple Combo Box Queries

[Sirkevin]

Please will someone help me with getting the correct results from my query using combo boxes in a form. I have set up a basic example with three query fields (ID, city, country). The combo boxes point to the table containg the same info (named: tbltrippy1). Query called: Qrytrippy2 and Form called: SearchForm2. Sorry about the names however just set this up to try and get to the bottom of my problem and thought it would be easier to post. I have set up a button to run the query from the form.

The SQL (written in the query page) is as follows:

SELECT tbltrippy1.ID, tbltrippy1.city, tbltrippy1.country
FROM tbltrippy1
WHERE (((tbltrippy1.ID)=[Forms]![SearchForm2]![Combo21])) OR (((tbltrippy1.ID)=[Forms]![SearchForm2]![Combo23]) AND (([Forms]![SearchForm2]![Combo21]) Is Null)) OR (((tbltrippy1.ID)=[Forms]![SearchForm2]![Combo25]) AND (([Forms]![SearchForm2]![Combo23]) Is Null)) OR ((([Forms]![SearchForm2]![Combo25]) Is Null));

sorry did this in a rush (Combo 21 is ID, Combo 23 is City and combo 25 is Country)

Unless all three combo boxes are completed all of the table values are shown. If all three are input the correct results are shown. I am trying to work out how to only get the fields matching the input criteria from the form to be shown e.g if data was:

ID city country
1 Berlin Germany
2 Paris France
3 London England
4 Canberra Australia
5 Washington DC America
6 Rome Italy
7 Oslo Norway

If 1, Berlin or Germany (or any 1 of the three) was entered it would return (1, Berlin, Germany) and the same for the others. I have tried adding a Like bit to the SQL however if I entered 1 and Paris it would return both the data on ID Line 1 and ID line 2 instead of returning nothing. I would like only 'all' matched fields to be shown (i.e. if ID 1 and Rome was entered nothing would be returned as both fields do not match the table data).

I hope that this makes sense, I am pretty new to this and it has been buggin me for days.

Any help would be appreciated thanks.

 

[Access_guy49]

your SQL should be:


SELECT tbltrippy1.ID, tbltrippy1.City, tbltrippy1.Country
FROM tbltrippy1
WHERE (((tbltrippy1.ID)=[Forms]![SearchForm2]![Combo21])) OR (((tbltrippy1.City)=[Forms]![SearchForm2]![Combo23])) OR (((tbltrippy1.Country)=[Forms]![SearchForm2]![Combo25]));

You have 2 problems in your SQl, firstly, you are always using the Id field for every combobox. So that will cause you some problems, and secondly, you are using AND.

To get a better idea of AND's and OR's think of it this way:

A girl is going to go to the prom. She has several criteria to meet. Her date must:
Be Tall AND Well Dressed, AND have a car. if the date does not have any one of these criteria he will not be going with her.

However, another girl is going to prom with slightly less picky criteria for her date.
He must:
Be good looking OR be tall OR have a car.
A man who is short but has a car will still be able to go with this girl.

Hope this helps

 

[Access_guy49]

sorry, i got confused on the last section of your post. This wont work because it will return values for 1 as well as Rome (as per the example you used.) my mistake.

 

[Access_guy49]

ok hopefully this will redeem myself.


SELECT tbltrippy1.ID, tbltrippy1.City, tbltrippy1.Country
FROM tbltrippy1
WHERE (((tbltrippy1.ID)=IIf([Forms]![SearchForm2]![Combo23] Is Null And [Forms]![SearchForm2]![Combo25] Is Null,[Forms]![SearchForm2]![Combo21],Null))) OR (((tbltrippy1.City)=IIf([Forms]![SearchForm2]![Combo21] Is Null And [Forms]![SearchForm2]![Combo25] Is Null,[Forms]![SearchForm2]![Combo23],Null))) OR (((tbltrippy1.Country)=IIf([Forms]![SearchForm2]![Combo23] Is Null And [Forms]![SearchForm2]![Combo21] Is Null,[Forms]![SearchForm2]![Combo25],Null))) OR (((tbltrippy1.ID)=[Forms]![SearchForm2]![Combo21]) AND ((tbltrippy1.City)=[Forms]![SearchForm2]![Combo23]) AND ((tbltrippy1.Country)=[Forms]![SearchForm2]![Combo25])) OR (((tbltrippy1.ID)=[Forms]![SearchForm2]![Combo21]) AND ((tbltrippy1.City)=[Forms]![SearchForm2]![Combo23])) OR (((tbltrippy1.ID)=[Forms]![SearchForm2]![Combo21]) AND ((tbltrippy1.Country)=[Forms]![SearchForm2]![Combo25])) OR (((tbltrippy1.City)=[Forms]![SearchForm2]![Combo23]) AND ((tbltrippy1.Country)=[Forms]![SearchForm2]![Combo25]));

 

[Sirkevin]

Thanks for the code, however it does not seem to work. I have copied and pasted it into the query, however when the criteria is entered the following occurs:

if ID is entered the correct field is shown
if the city or country is included from the combo drop dowms on my form, no data is returned, even if all of the data is correct for a record.

i.e. if the parameters from the drop down were:

ID = 1 the correct field is shown
ID = 1, City = Berlin nothing is shown
ID = 1, City = Berlin & Country = Germany nothing is shown
The same happens if City or country is entered without the ID

I need the correct record to be pulled up if any one of the three parameters is entered.

Hope you can help to solve

Cheers

 

[Access_guy49]

I have created a simple database. it has a search form, give it a try and tell me if it works, I tried it out and it seemed to work fine, and the code is the same from what i can see. Perhaps you can use this sample database to help you out.

The search worked for Berlin, and Germany, but when Id was added to the criteria as 2, it did not work. I also tried ID 1 and Berlin. Seemed to work fine.

Hope this can help you!

 

[Sirkevin]

Thanks for this. Your database is very close to being correct, however it is still experieincing the problems I was having originally.

For example:

2 and London = zero returns (correct)
2 London and England = 3 London England (incorrect)

The three criteria together should not find a positive match (one does not exist)

Same for 1 Berlin France = 1, Berlin, Germany (incorrect)

I am sure we are nearly there though!!!!

 

[Access_guy49]

Give this one a shot! (Fingers crossed!)

 

[Sirkevin]

Well done, this one is spot on.

Thank you very much, your help is much appreciated.

I now need to amend it to get it to work for around 20 fields in a new database I am designing

 

[Sirkevin]

Hey, could I be a real pain and ask you to reupload your database.

I dont know if someone has amended it, but it now appears to be read only and the search options no longer work.

Unfortunately I did not save your original to my pc.

 

[rockyjr]

Just a question,

This is actually what I was looking for a long time.... I love it, and it works great. If I would like to add that if all fields are null... to show all? How would I write this?

 

[Access_guy49]

Sirkevin - Here it is again, wierd that it just stopped working. Glad to help, I hope it goes well with your other fields. Let me know if this one has the same readonly lockout. (it shouldn't)

 

[Access_guy49]

RockyJr - I have created another slightly modified example for you. the additional feature you requested is not in the query. The code is placed inside the button that activates the search. (this was done due to conflicts occuring when the IFF statements were added to the existing query.) Hope it works for you!

 

[rockyjr]

I dont know how to thank you enough!!!!

This is awesome.....

 

[Sirkevin]

Thanks Access Guy, sorry for the late reply, however I have been abroad for about 3 weeks and it has been a bit hectic.

I did not know if you would still be subscribed to this thread, hence I have reposted a new one on the queries section, "Multiple Queries and Drop Downs".

I am still having difficulty with it so have posted part of the database online. It would be great if you could take a look (that is if you get this post).

I know part of the SQL is missing, however it was getting a bit complicated and I think I need a bit of help.

Thanks in anticipation

K