MS Access Query OR Criteria

I have been struggling with this one. I don't understand why it does not work. In my query criteria if I use OR it never works.

If I use:
<> "US Postal Service" Or "Shipping" Or "Joes Inc."

It always returns "Shipping"  "Joes Inc." with the other results.

What am I doing wrong????
LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?
 
Ryan ChongCommented:
The correct expression would use  ANDs as the relational operator.

yourfield <> "US Postal Service" and yourfield <> "Shipping" And  yourfield <> "Joes Inc."
yea, there was a typo for second condition in which it should be as "and" instead of "or".

thanks Pat for correction anyway.
0
 
Ryan ChongCommented:
you need to provide the field name as well, and i guess you mean this:

yourfield <> "US Postal Service" and yourfield <> "Shipping" Or  yourfield <> "Joes Inc."

Open in new window


or use in clause, like:

yourfield not in ( "US Postal Service" , "Shipping" , "Joes Inc." )

Open in new window

0
 
PatHartmanCommented:
Ryan is correct in his assessment of your syntax error however.  Due to the logic error, the expression will return everything rather than what you think it should return because only one of the OR conditions needs to be true and since yourfield cannot possibly be two different values, one of the conditions will ALWAYS be true.

The correct expression would use  ANDs as the relational operator.

yourfield <> "US Postal Service" and yourfield <> "Shipping" And  yourfield <> "Joes Inc."

Ryan's suggestion to use IN() is more concise and better once you have more than two values.

SQL uses the Boolean logic we know and love from Algebra I.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Dustin StanleyEntrepreneurAuthor Commented:
Perfect! I did not think about putting [YourField] in there. Thanks for the help.
0
 
PatHartmanCommented:
Use the QBE to build queries if you don't know the syntax.  Then if you absolutely must use the SQL embedded in VBA rather than the saved querydef, switch to SQL view and copy the string into your code.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Sounds good! Thank you for the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.