Query criteria conflict

[Guest]

Hi - I've been trying to set criteria in a query to return certain records
from two different fields. When I set the criteria for both fields, only the
first field's criteria seems to work. The second field's criteria are not
applied. Both sets of criteria work on their own, just not together. What
I've had to do is apply one set of criteria and then apply a filter in the
resulting query. Is there any way around this? Thanks!

 

[Guest]

What do you want 'field1 = value and field2 = value' or 'field1=value or
field2 = value'?
Here are the where clauses
WHERE (((table.field1)=1000)) OR (((table.field2)="NY"));
this will give all records with either value equal
WHERE (((table.field1)=1000)) AND (((table.field2)="NY"));
this will give only records with both values are equal

With out more info this is the best I could do...

HTH
Martin

 

[John Vinson]

Hi - I've been trying to set criteria in a query to return certain records
from two different fields. When I set the criteria for both fields, only the
first field's criteria seems to work. The second field's criteria are not
applied. Both sets of criteria work on their own, just not together. What
I've had to do is apply one set of criteria and then apply a filter in the
resulting query. Is there any way around this? Thanks!

Please post the SQL view of the query that's not working. You may need
to use parentheses to properly nest the AND and OR logic, but without
seeing the query it's hard to say!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks for your reply. I'm looking to retrieve records that match both sets
of criteria - i.e. records in my asset field that match "server" AND records
in the status field that match "online" so that I only get a list of the
servers that are online.

 

[Guest]

Thanks for your reply. The SQL view is as follows:

SELECT Assets.AssetID, Assets.AssetDescription, [Asset
Categories].AssetCategory, Assets.EmployeeID, Status.Status
FROM Status RIGHT JOIN (Employees RIGHT JOIN ([Asset Categories] RIGHT JOIN
Assets ON [Asset Categories].AssetCategoryID = Assets.AssetCategoryID) ON
Employees.EmployeeID = Assets.EmployeeID) ON Status.StatusID = Assets.StatusID
WHERE ((([Asset Categories].AssetCategory)="Workstation") AND
((Status.Status)="On-Line")) OR ((([Asset
Categories].AssetCategory)="Server")) OR ((([Asset
Categories].AssetCategory)="Laptop"));

 

[Guest]

Thanks to all who replied. I finally got my query to work with both sets of
criteria. My query was based on a table that's linked to several other
tables. By using the same field in one of the linked tables to set my
criteria I was able to get it to work. I have no idea why, though, so if
anyone has a theory, I'd love to know. I'm happy its working now, though!

 

[John Vinson]

SELECT Assets.AssetID, Assets.AssetDescription, [Asset
Categories].AssetCategory, Assets.EmployeeID, Status.Status
FROM Status RIGHT JOIN (Employees RIGHT JOIN ([Asset Categories] RIGHT JOIN
Assets ON [Asset Categories].AssetCategoryID = Assets.AssetCategoryID) ON
Employees.EmployeeID = Assets.EmployeeID) ON Status.StatusID = Assets.StatusID
WHERE ((([Asset Categories].AssetCategory)="Workstation") AND
((Status.Status)="On-Line")) OR ((([Asset
Categories].AssetCategory)="Server")) OR ((([Asset
Categories].AssetCategory)="Laptop"));

Ok, let's see if this can be untangled. Getting rid of some of Access'
extra parentheses we get:

SELECT Assets.AssetID, Assets.AssetDescription,
[Asset Categories].AssetCategory, Assets.EmployeeID, Status.Status
FROM Status RIGHT JOIN (Employees RIGHT JOIN ([Asset Categories] RIGHT
JOIN Assets ON [Asset Categories].AssetCategoryID =
Assets.AssetCategoryID) ON Employees.EmployeeID = Assets.EmployeeID)
ON Status.StatusID = Assets.StatusID
WHERE
[Asset Categories].AssetCategory="Workstation"
AND Status.Status)="On-Line"
OR
([Asset Categories].AssetCategory)="Server"
OR ([Asset Categories].AssetCategory="Laptop");

That is, it will retrieve the record if it's an online workstation; OR
if it's either a Server or a Laptop. Since you're connecting these by
OR, it will retrieve all the Servers and all the Laptops regardless of
whether they're online or not.

Could you maybe draw up a little "truth table" indicating which
records should be retrieved? I'm not sure of the logic you're trying
to attain!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps