Using words with apostrophes in filter

[Alex Ng]

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

[MacDermott]

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

[Alex]

"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 & """"

[Uttam]

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 & """"