Search Form

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Hello. I have a form that builds an SQL statement
depending on what fields you type data into. It all
works fine until I search for a name with an apostrophe
(') in it, such as "Papa John's".

The code I have so far is below:
If txtACCOUNT_NAME <> "" Then
strqry = strqry & "((ACCOUNT_ARCHIVE.ACCOUNT_NAME)
LIKE '%" & txtACCOUNT_NAME & "%') AND "
End If

This is just a section on the SQL statement that the form
builds. But how can I make it accept the apostrophe?

After searching for "john's", the error I get is:

Syntax error (missing operator) in query
expression '(((ACCOUNT_LIST.ACCOUNT_NAME) LIKE '%
john's%'))'.

Any help would be great. Thanks.

Brian
 
Wrap the control reference with the Replace function to double up the '
character (that allows ACCESS to see it as a single, embedded ' character in
the text string):

strqry = strqry & "((ACCOUNT_ARCHIVE.ACCOUNT_NAME) LIKE '%" &
Replace(txtACCOUNT_NAME, "'", "''", 1, -1, vbTextCompare) & "%') AND "
 
Works great!! Thank you!

-----Original Message-----
Wrap the control reference with the Replace function to double up the '
character (that allows ACCESS to see it as a single, embedded ' character in
the text string):

strqry = strqry & "((ACCOUNT_ARCHIVE.ACCOUNT_NAME) LIKE '%" &
Replace(txtACCOUNT_NAME, "'", "''", 1, -1, vbTextCompare) & "%') AND "


--

Ken Snell
<MS ACCESS MVP>




.
Click to expand...
 
Back
Top