 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2006Why do Access queries stop working if I use more than 2 criteria?
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"  SignatureGood Luck BS"D > 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. 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? > Hi Nora > Can you post the SQL? [quoted text clipped - 13 lines] > > It means that I can't look for specific data. > > Am currently using Micrsoft Access 2003. 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") SignatureGood Luck BS"D > SQL's > SELECT tbl_Contact.Date, tbl_Contact.Activity, Tbl_Registration.Gender, [quoted text clipped - 26 lines] > > > It means that I can't look for specific data. > > > Am currently using Micrsoft Access 2003. 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") SignatureGood Luck BS"D > SQL's > SELECT tbl_Contact.Date, tbl_Contact.Activity, Tbl_Registration.Gender, [quoted text clipped - 26 lines] > > > It means that I can't look for specific data. > > > Am currently using Micrsoft Access 2003. 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 > SQL's > SELECT tbl_Contact.Date, tbl_Contact.Activity, Tbl_Registration.Gender, [quoted text clipped - 29 lines] > > > It means that I can't look for specific data. > > > Am currently using Micrsoft Access 2003. 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 > SQL's > SELECT tbl_Contact.Date, tbl_Contact.Activity, Tbl_Registration.Gender, [quoted text clipped - 29 lines] > > > It means that I can't look for specific data. > > > Am currently using Micrsoft Access 2003. 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 > Nora > [quoted text clipped - 40 lines] > > > > It means that I can't look for specific data. > > > > Am currently using Micrsoft Access 2003. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.