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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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"
 
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?
 
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")
 
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")
 
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
 
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
 
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
 
Back
Top