Why do Access queries stop working if I use more than 2 criteria?

N

Nora

My queries work fine up to a point but if I enter any more than 2 criteria it
seems to 'drop' one of them.
It means that I can't look for specific data.
Am currently using Micrsoft Access 2003.
 
O

Ofer Cohen

Hi Nora
Can you post the SQL?

But before, In your query criteria if you specified "Or" it will 'drop' one
of the conditions.
If you want both conditions you should use "And"

So, if you have
Select * From TableName Where FieldNAme1 = 1 Or FieldName2 = "dddd"

Change it to
Select * From TableName Where FieldNAme1 = 1 And FieldName2 = "dddd"
 
N

Nora

SQL's
SELECT tbl_Contact.Date, tbl_Contact.Activity, Tbl_Registration.Gender,
Tbl_Registration.DOB, Tbl_Registration.Ethnic_origin
FROM Tbl_Registration INNER JOIN tbl_Contact ON Tbl_Registration.NI =
tbl_Contact.NI
WHERE (((tbl_Contact.Date) Between #4/28/2005# And #5/30/2006#) AND
((tbl_Contact.Activity)="gen")) OR (((tbl_Contact.Activity)="jo")) OR
(((tbl_Contact.Activity)="js"));

I can see the logic of what you're saying but I can't seem to find how to
change to 'AND' instead of 'OR' in the design view. Is this where I need to
change it or should it be somewhere else?
 
O

Ofer Cohen

You can change it through the SQL view, and then you can see the changes in
design view

Is that you are trying to achieve: (copy and paste in a new query)

Select tbl_Contact.Date, tbl_Contact.Activity, Tbl_Registration.Gender,
Tbl_Registration.DOB, Tbl_Registration.Ethnic_origin
FROM Tbl_Registration INNER JOIN tbl_Contact ON Tbl_Registration.NI =
tbl_Contact.NI
WHERE (tbl_Contact.Date Between #4/28/2005# And #5/30/2006#) AND
(tbl_Contact.Activity="gen" OR tbl_Contact.Activity="jo" OR
tbl_Contact.Activity="js")
 
O

Ofer Cohen

You can make it shorter

SELECT tbl_Contact.Date, tbl_Contact.Activity, Tbl_Registration.Gender,
Tbl_Registration.DOB, Tbl_Registration.Ethnic_origin
FROM Tbl_Registration INNER JOIN tbl_Contact ON Tbl_Registration.NI =
tbl_Contact.NI
WHERE (tbl_Contact.Date Between #4/28/2005# And #5/30/2006#) AND
tbl_Contact.Activity In ("gen","jo","js")
 
D

dave

Nora

Is your issue that, in the design view, all expressions on a single
Criteria line are ANDed together and that multiple Criteria lines are
ORed?

Therefore, in your example, the check on contract date should be
repeated on all three Criteria lines.

Dave
 
D

dave

Nora

Is your issue that, in the design view, all expressions on a single
Criteria line are ANDed together and that multiple Criteria lines are
ORed?

Therefore, in your example, the check on contract date should be
repeated on all three Criteria lines.

Dave
 
N

Nora

Ofer and Dave,

Thank you both very much.
Have now solved the problem and will be able to submit my monthly report
accurately and on time.
And have learned something new about Access!

many thanks

Nora
 
Top