Type Mismatch Error.

[gsrajan]

I am getting type mismatch error for the following syntax. Kindly let me know what I am missing. When I remove the and City Like "" * " & Text49 & " * """ there is no error message. Thanks.

Dim dbs As Database
Dim rst As Recordset
Dim myString As String

On Error GoTo errorhandler

Set dbs = CurrentDb()
myString = "SELECT * FROM Masterqry WHERE Zip ='" & txtZipSearch & "' and soc_code='" & CboSOC & "' and City Like "" * " & Text49 & " * """

Set rst = dbs.OpenRecordset(myString)
If rst.RecordCount < 1 Then
MsgBox "No record found"
Cancel = True
Exit Sub
End If
Me.RecordSource = myString

Me.Refresh
Exit Sub
errorhandler:
Cancel = True
End Sub

 

[plog]

You lost track of double and single quotes.

Check your bolded text and use single quotes appropriately.

 

[MajP]

Not sure if the spaces will make a difference ' * versus '* but I would get rid of those
Like '*" & Text49 & "*'"

 

[Cronk]

Not sure if the spaces will make a difference

It would require the city record to have at least a leading and trailing space to be selected.

@gsrajan, Add the line below to your code to check exactly what sql string you are generating

debug.print myString