getting syntax error in string in query expression 1

[Chrisma]

I am trying to search a surname that start from Mc' or O' but everytime i type that in a text box and click the command search i recieved a message

Syntax error in string in query expression 'INDIVIDUAL_CLIENT.surname like 'O" ORDER BY INDIVIDUAL_CLIENT.surname;"


my coding:

StrSQL = "SELECT INDIVIDUAL_CLIENT.CLIENT_ID,"
StrSQL = StrSQL & " INDIVIDUAL_CLIENT.SURNAME & ', ' & INDIVIDUAL_CLIENT.GIVEN_NAME AS CLIENT_FULL_NAME,"
StrSQL = StrSQL & " INDIVIDUAL_CLIENT.DOB, INDIVIDUAL_CLIENT.FILE_NUMBER"
StrSQL = StrSQL & " FROM INDIVIDUAL_CLIENT"
StrSQL = StrSQL & StrWhere
StrSQL = StrSQL & " ORDER BY INDIVIDUAL_CLIENT.SURNAME"
StrSQL = StrSQL & ";"

please help me.......

 

[PHV]

Any chance you could post how StrWhere is build ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Chrisma]

my front end is MS Access and my back end is MS SQL server.



Dim StrSQL, StrWhere As String

If Not (IsNull(TheName.Value)) Then
StrWhere = "INDIVIDUAL_CLIENT.SURNAME Like '" & TheName.Value & "'"
End If
StrSQL = "SELECT INDIVIDUAL_CLIENT.CLIENT_ID,"
StrSQL = StrSQL & " INDIVIDUAL_CLIENT.SURNAME & ', ' & INDIVIDUAL_CLIENT.GIVEN_NAME AS CLIENT_FULL_NAME,"
StrSQL = StrSQL & " INDIVIDUAL_CLIENT.DOB, INDIVIDUAL_CLIENT.FILE_NUMBER"
StrSQL = StrSQL & " FROM INDIVIDUAL_CLIENT"
StrSQL = StrSQL & StrWhere
StrSQL = StrSQL & " ORDER BY INDIVIDUAL_CLIENT.SURNAME"
StrSQL = StrSQL & ";

 

[PHV]

Ac2k and above:[tt]
StrWhere = "INDIVIDUAL_CLIENT.SURNAME Like '" & Replace(TheName.Value, "'", "''") & "'"[/tt]

I wonder you don't use a wildcar ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886