QueryDef Multiple Criteria

[Guest]

I'm trying to open a querydef using a SQL statement that has multiple
criteria. My database has three locations: A, B and C. If I use the
following SQL statement, only the A locations are returned (which is correct):

Dim strSQL as string
strSQL = "SELECT * FROM tblPatients WHERE LocationFilter = 'A'"
CurrentDb.QueryDefs("qryPatientsLName").SQL = strSQL

However, if I try to query two locations using the following statement:

strSQL = "SELECT * FROM tblPatients WHERE LocationFilter = 'A' Or 'B'"

all THREE locations are returned. What am I doing wrong? Thanks.

 

[Allen Browne]

You need to repeat the field name:
strSQL = "SELECT * FROM tblPatients WHERE (LocationFilter = 'A') Or
(LocationFilter = 'B');"

An alternative if you have more options would be to use the IN operator:
strSQL = "SELECT * FROM tblPatients WHERE LocationFilter IN ('A', 'B');"

 

[Guest]

Allen,

Many thanks! I tried the IN operator and it worked great.

Question: Why do you use a semi-colon at the end of your SQL statements?

 

[Allen Browne]

The semicolon is not required, but it is the standard.

Here's an example, chosen because it is not specifically Access related:
http://sqlcourse.com/select.html