Syntax error(missing operator)

[crysma]

Hi!

I've been working on a program that will help us keep track of our subscribers. Among other things, a user should be able to find a subscribers Zone number by entering their community name. This works for most part, except if the community name has an apostrophe in it(and you wouldnt belive how many of those there are!). Then it throws the following error:

2147217900(80040e14) Syntax error (operator missing) in query expression 'Community='Jean D'Or''

I'd really appreciate any tips or pointers on how to get around this problem. The code snippet follows:

strSQL = "SELECT * FROM tblCommunity WHERE Community = '" & txtCommunity.Text & "'"
With rsZoneNo
.ActiveConnection = cnCon
.Source = strSQL
.LockType = adLockOptimistic
.CursorLocation = adUseServer
.Open
End With
intZoneNo = rsZoneNo("ZoneNo&quot
rsZoneNo.close


Thanks,

Sheryll

 

[MarkSweetland]

Replace all occurances of single quotes with two single quotes:

strSQL = Replace(strSQL, Chr(39), Chr(39) & Chr(39))

Mark

 

[crysma]

Hi Mark, Thanks for that. Unfortumately, its still not working. It is still throwing the same error

Sheryll

 

[LazyMe]

Still it has to be what Mark said. Only be careful not to replace the surrounding single quotes by two single quotes. Only the value that comes from your txtCommunity.Text must be parsed that way. It's because the database engine sees the single quotes as string delimeters.

i.e:

strSQL = "SELECT * FROM tblCommunity WHERE Community = '" & Replace(txtCommunity.Text, "'", "''&quot & "'"

Greetings,
Rick

 

[crysma]

Thanks you were right. I just tried it again

Thanks again Mark and Rick

Sheryll