Help With "Like" in strSQL

[S Jackson]

I am attempting to write this statement:

strSQL = "Select tblSurveyors.SvyFirstName, tblSurveyors.SvyLastName, " _
& "tblSurveyors.SvyCity, tblSurveyors.Region, tblSurveyors.SvyTitle " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & Me.Region & " " _
& "AND WHERE tblSurveyors.SvyTitle LIKE Program*" _
& "ORDER BY tblSurveyors.svyLastName;"
Me.cmbFieldOfc.RowSource = strSQL
MsgBox strSQL

When I test this, I get this error when clicking on the cmbFieldOfc combo
box:

Syntax error (missing operator) in query expression "tblSurveyors.Region =
10 AND WHERE tblSurveyors.SvyTitle LIKE Program*'.

I figure it has to be the strSQL statement in the Like expression, but I've
been testing different things, and I am not having much luck. I can never
remember when to use single or double quotes. I tried this:

& "AND WHERE tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "'" _

But I still get the missing operator error.

TIA
S. Jackson

 

[Ken Snell [MVP]]

These lines do not have a space between the * and the ORDER words:
& "AND WHERE tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "'" _
& "ORDER BY tblSurveyors.svyLastName;"


Try this:
& "AND WHERE tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "' " _
& "ORDER BY tblSurveyors.svyLastName;"

 

[S Jackson]

Thanks, but I still getting a syntax error:
'tblSurveyors.Region = 10 AND WHERE tblSurveyors.SvyTitle LIKE 'Program*".

Note that there is a single quote before Program and then a double quote
after the asterisk. Is this the problem? Where to put all the quotes
confuses the hell out of me!

Also, just so I haven't confused you, the msgbox=strSQL works fine - the SQL
statement appears correct. The error occurs when I attempt to pull down the
combo box cmbFieldOfc.

 

[S Jackson]

Solved the problem. Turned out to be simple too. I took out the extra
"WHERE" in the statement. It reads like this:

& "AND tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "' " _

Thanks! (I am still confused about where the & and the quotes go, but maybe
some day I'll figure it out!)

 

[Ken Snell [MVP]]

Sorry... I had overlooked the doubling of WHERE.

When using Like operator, you must use delimiting quote characters (either '
or ") for the value being matched. Usually, you can use either ' or "; it's
usually easier to use ' within a text string that you're building so that
you don't need to double up the " characters. But be careful if the matching
value contains a ' character; it will then prematurely "terminate" the text
string unless you double it up too.
--

Ken Snell
<MS ACCESS MVP>