Query based on multiple criteria, show only results that match both criteria (1 Viewer)

Scaniafan

Registered User.
Local time
Today, 03:26
Joined
Sep 30, 2008
Messages
82
Hello all,

I'm trying to build an query which uses several criteria. I have build the criterias:

- Depot
- Brand
- Chassis height
- Low Emission Zone
- Umwelt-sticker
- Euro

All are comboboxes and every criteria works as I want them to work. So, if I select a depot and run the query, only the trucks that drive for that depot are shown etc. When I leave all the comboboxes blank, the entire fleet is shown.

So far so good.

Now the problem.

When I select 2 criteria (e.g. Depot "Romania" and chassis height "95 / 112" ), the result of the query is that:

- All the trucks in Depot Romania are shown

and

- All the trucks in the fleet which have a chassis heigt of 95 / 112 cm are shown.

I just want to see the trucks that are in Depot Romania with a chassis height of 95 / 112 cm.


What access does is running the query and find results to either one of the querys, while I want to find results that match both criteria.

Can someone help me please?
 

Scaniafan

Registered User.
Local time
Today, 03:26
Joined
Sep 30, 2008
Messages
82
Never mind, I found out what I did wrong.

I placed all the criteria in a seperate "Or"-line in the Query Designer.

After placing the criteria in te "Criteria"-line and beneath that in the "Or"-line, but in the "Or"-line with "Is Null" behind the criteria, it worked :)

--------------

Ok.......happy to early

After testing it seems that this only works with maximum 2 criteria... So if someone still has an answer how to make it working, please let me know...
 
Last edited:

Rabbie

Super Moderator
Local time
Today, 10:26
Joined
Jul 10, 2007
Messages
5,906
Are you using the Query design grid? If so both criteria should be on the same horizontal line. If they are on different lines then it does an OR on your selections.

Tt would be helpful if you can post the SQL for your query if you are still getting the problem.

Your query should have a clause like

WHERE depot like "Romania" and Chassis like "95 / 112"

The like may be = in some cases.
 

Kiwiman

Registered User
Local time
Today, 10:26
Joined
Apr 27, 2008
Messages
799
do you have the criteria on the same line? One way of getting the result above is to have one criteria on one line and the other on another line. This will act like an "OR" statement, and will return records that meet either of the criteria.
 

Scaniafan

Registered User.
Local time
Today, 03:26
Joined
Sep 30, 2008
Messages
82
do you have the criteria on the same line? One way of getting the result above is to have one criteria on one line and the other on another line. This will act like an "OR" statement, and will return records that meet either of the criteria.


At the moment I've all the criteria on different lines, like this:

http://img383.imageshack.us/my.php?image=queryfleetlistgk4.png

I've tried putting all the six criteria on one line, but than the results are always blank. Therefore, I'm guessing that I can't use more than 2 criteria together.
 

Kiwiman

Registered User
Local time
Today, 10:26
Joined
Apr 27, 2008
Messages
799
try inserting the following into each of your criteria, and put on one line.

Code:
Like "*" & [yourcurrent criteria] & "*"
 

Scaniafan

Registered User.
Local time
Today, 03:26
Joined
Sep 30, 2008
Messages
82
try inserting the following into each of your criteria, and put on one line.

Code:
Like "*" & [yourcurrent criteria] & "*"

You're a God to me :p My collegue was already laughing at me that it would never work, so now I can laugh at him :D

-----------------------------

But now the next problem occurs. When there is no data for one of the criteria, (whether I've selected the criteria or leave it blank), the query doesn't show a result.

E.g.:

Both trucks in Barcelona don't have a entry in their "chassis height" field. When I have the query look for the depot "Barcelona" and no other criteria, neither of the trucks show up....
 
Last edited:

Scaniafan

Registered User.
Local time
Today, 03:26
Joined
Sep 30, 2008
Messages
82
And also that problem is solved, just a phone-call to the fleetmanager to complete the missing data in the fleetlist :D

Thanks all for helping me out!
 

jerich

New member
Local time
Today, 03:26
Joined
May 2, 2014
Messages
1
Never mind, I found out what I did wrong.

I placed all the criteria in a seperate "Or"-line in the Query Designer.

After placing the criteria in te "Criteria"-line and beneath that in the "Or"-line, but in the "Or"-line with "Is Null" behind the criteria, it worked :)

--------------

Ok.......happy to early

After testing it seems that this only works with maximum 2 criteria... So if someone still has an answer how to make it working, please let me know...

This worked for my brother: after giving the first criteria and beneath it in the "Or" line give the second criteria, then get out of design view and get into it again then it will show the second criteria also already in the first line and you will again have the option beneath it to insert the third criteria in the "Or" line. Apart from the hassle of getting out of the design view and going back into it again, this works with any number of criteria in the same column.
 

jeni_ojeni

New member
Local time
Today, 03:26
Joined
Sep 13, 2016
Messages
5
i have several tables and need information from each table BUT not all tables will have info. The problem is that it won't populate a response unless all criteria's apply. I think I need more like and/if in the criteria but not sure how to do that.

I have the following tables: Drawing, data list, Spec and Reference. Not all drawings will have both spec and reference, some will just have spec. All drawings will have a data list.

I ran the query and only drawings that have ALL come up. Not sure how to go about this and I m such a newbie.

thanks.
 

plog

Banishment Pending
Local time
Today, 05:26
Joined
May 11, 2011
Messages
11,611
It's the thread that just won't die. Started almost 9 years ago, went dormant for 5.5 years then dormant again for over 3. I'm making a mental note to check this thread during the next summer olympics.

Jeni, you should start a new thread for your issue. In that thread post a screenshot of your relationship tool so that we can see all your tables and their fields.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:26
Joined
May 7, 2009
Messages
19,169
Don't use Inner Join on your Query, use Left Join:
 

Users who are viewing this thread

Top Bottom