VB Query Problem

I have a problem to create a query in vb which use access database. I want to search an employername John's Cafe from table EmployerInfo
The query as follow:
Stsql = "Select * from EmployerInfo where EmployerName = 'John's Cafe' "
It shows the syndax Error message!

Anybody knows how to solve this query. Thank you.

The query looks OK to me.

i think problem is here << 'John's Cafe' >>

try this

dim tex as string
tex= "John's Cafe"
Stsql = "Select * from EmployerInfo where EmployerName = ' "+ tex +" ' "

Psaxtiris has better eyesight The embedded ' in John's Cafe is probably the problem.

For MS databases, you need to escape the single ' with two of them.

Stsql = "Select * from EmployerInfo where EmployerName = 'John' 's Cafe' "

You can use the Replace function to turn all the single apostrophes into double one's (ignore extra spaces added for readability):

str = " 'John's Cafe' "
str = Replace (str, " ' ", " ' ' ")

HELLO,

The Queries having special caracters especially Single Quote i.e. '; Double Quote i.e. " and Pipe i.e. | will always give you problem

as the query that you have used contains single Quote it is obvious to give you problem.

Stsql = "Select * from EmployerInfo where EmployerName = 'John's Cafe' "

for that you can either reatrict these caracters by checking the keyascii in key press event or keycode in keydown event

E.g.
If KeyAscii = 34 Or KeyAscii = 39 Or KeyAscii = 124 Then
KeyAscii = 0
Exit Sub
End If

this code will prevent the user from entering the special caracters.

or you have to attach One more same special caracter along with it as shown below.

Select * from CustomerMaster where CustName = 'John''s Cafe'

Hi!

Here is the code i have and it works but some last data in the memo field is missing.

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click

Dim oApp As Object
Dim SvcList As Control
Dim varItem As Variant
Dim SvcTitle As String
Dim SvcDetail As String



Set SvcList = Me!SvcTitleList

Set oApp = GetObject(, "Word.Application")


For Each varItem In SvcList.ItemsSelected

SvcTitle = Nz(SvcList.Column(1, varItem)) & vbCrLf

SvcDetail = Nz(SvcList.Column(1, varItem)) & vbCrLf & vbCrLf & _
Nz(SvcList.Column(2, varItem)) & vbCrLf & vbCrLf


With oApp
.ActiveDocument.Bookmarks("Services").Range.Text = SvcTitle
.ActiveDocument.Bookmarks("SvcDetailBM").Range.Text = SvcDetail

End With


Next varItem
Exit_Command16_Click:
Exit Sub

Err_Command16_Click:
MsgBox Err.Description
Resume Exit_Command16_Click

End Sub

Thanks again,
tkapal