QueryDef Multiple Criteria

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

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