By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,459 Members | 2,284 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,459 IT Pros & Developers. It's quick & easy.

Using words with apostrophes in filter

P: n/a
Hi,

I have a form with an unbound textbox for entering a name to search for with

strSQL = "SELECT * FROM main " & "WHERE main.[1_surname] = ' " &
Me.SurnameSearch & " ' "

this works fine for the filter, but however, everytime I want to serach for
a name with an apostrophe in it (e.g. O'Grady), an error would turn up.
I know it has got something to do with the apostrophe in the name confusing
with the aphostrophe used in my strSQL. But how to I overcome this problem?

Thanx
Alex
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
One way I sometimes address this is to "escape" the apostrophe, like this:
Replace(Me.SurnameSearch,"'","''")
Here it is with added spaces for clarity: (Don't use it in this form,
though.)
Replace(Me.SurnameSearch," ' "," ' ' ")

HTH
- Turtle

"Alex Ng" <au**********@hotmail.com> wrote in message
news:bk**********@lust.ihug.co.nz...
Hi,

I have a form with an unbound textbox for entering a name to search for with
strSQL = "SELECT * FROM main " & "WHERE main.[1_surname] = ' " &
Me.SurnameSearch & " ' "

this works fine for the filter, but however, everytime I want to serach for a name with an apostrophe in it (e.g. O'Grady), an error would turn up.
I know it has got something to do with the apostrophe in the name confusing with the aphostrophe used in my strSQL. But how to I overcome this problem?
Thanx
Alex

Nov 12 '05 #2

P: n/a
"Alex Ng" <au**********@hotmail.com> wrote in message news:<bk**********@lust.ihug.co.nz>...
Hi,

I have a form with an unbound textbox for entering a name to search for with

strSQL = "SELECT * FROM main " & "WHERE main.[1_surname] = ' " &
Me.SurnameSearch & " ' "

this works fine for the filter, but however, everytime I want to serach for
a name with an apostrophe in it (e.g. O'Grady), an error would turn up.
I know it has got something to do with the apostrophe in the name confusing
with the aphostrophe used in my strSQL. But how to I overcome this problem?

Thanx
Alex


Instead os using apostrophes uses double quotes eg
strSQL = "SELECT * FROM main " & "WHERE main.[1_surname] = """ &
Me.SurnameSearch & """"
Nov 12 '05 #3

P: n/a
For any special characters in a string I use the chr$(x) function
where the value of x could say 34 for double quotes, 39 for single
quotes etc.

To find what should be the value of a specific special character I use
the following simple method in Excel.

1) Open a blank worksheet
2) Enter 0 in A1
3) In A2 enter the formula "=A1+1" (only what is between the double
quotes).
4) Copy the formula from A2 down the column A upto A256
5) In B1 enter the formula "=CHAR(A1)" (only what is between the
double quotes).
6) Copy the formula in B1 down the column B upto B256.

Manually search for the special character down Column B and use the
corresponding value in Column A as your value of x.

Executing the 6 steps above takes less than a minute.

So in the example below, instead of the single quotes, use Chr$(39).

Hope this helps.

Cheers!

Uttam
================

Hope this helps.

al********@hotmail.com (Alex) wrote in message news:<49**************************@posting.google. com>...
"Alex Ng" <au**********@hotmail.com> wrote in message news:<bk**********@lust.ihug.co.nz>...
Hi,

I have a form with an unbound textbox for entering a name to search for with

strSQL = "SELECT * FROM main " & "WHERE main.[1_surname] = ' " &
Me.SurnameSearch & " ' "

this works fine for the filter, but however, everytime I want to serach for
a name with an apostrophe in it (e.g. O'Grady), an error would turn up.
I know it has got something to do with the apostrophe in the name confusing
with the aphostrophe used in my strSQL. But how to I overcome this problem?

Thanx
Alex


Instead os using apostrophes uses double quotes eg
strSQL = "SELECT * FROM main " & "WHERE main.[1_surname] = """ &
Me.SurnameSearch & """"

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.