Apostrophes in search field

  • Thread starter Thread starter Schlitterbahn
  • Start date Start date
S

Schlitterbahn

I am having trouble with search when the search field has
apostrophe in it (like Aaron's) because the findfirst
command use apostrophe and Access get confused where to
stop. I tried replacing them with double quote but it
didn't work.
 
Schlitterbahn said:
I am having trouble with search when the search field has
apostrophe in it (like Aaron's) because the findfirst
command use apostrophe and Access get confused where to
stop. I tried replacing them with double quote but it
didn't work.
Click to expand...

Replace them with two consecutive apostrophes (Aaron''s)
 
I am having trouble with search when the search field has
apostrophe in it (like Aaron's) because the findfirst
command use apostrophe and Access get confused where to
stop. I tried replacing them with double quote but it
didn't work.
Click to expand...

What did you try replacing? What you need to do is delimit the
FindFirst critrion in " instead of '. Could you post the code?

It should be something like

rs.FindFirst "[SearchField] = """ & strFind & """"

using the convention that two " characters are used to represent a
single " within a string constant delimited by ". Alternatively you
can use the fact that the ASCII value of " is 34:
rs.FindFirst "[SearchField] = " & Chr(34) & strFind & Chr(34)


Either of these will evaluate to

[SearchField] = "Aaron's"

which will work correctly.
 
-----Original Message-----
I am having trouble with search when the search field has
apostrophe in it (like Aaron's) because the findfirst
command use apostrophe and Access get confused where to
stop. I tried replacing them with double quote but it
didn't work.
Click to expand...

What did you try replacing? What you need to do is delimit the
FindFirst critrion in " instead of '. Could you post the code?

It should be something like

rs.FindFirst "[SearchField] = """ & strFind & """"

using the convention that two " characters are used to represent a
single " within a string constant delimited by ". Alternatively you
can use the fact that the ASCII value of " is 34:
rs.FindFirst "[SearchField] = " & Chr(34) & strFind & Chr (34)


Either of these will evaluate to

[SearchField] = "Aaron's"

which will work correctly.


.
Click to expand...
Here is the original code:
rs.FindFirst "[BUS_NAME] = '" & Me![List216] & "'"
I actually tried double quote instead of apostrophe before
I posted the question and it didn't work. Something like:
rs.FindFirst "[BUS_NAME] = "" & Me![List216] & """
 
-----Original Message-----
I am having trouble with search when the search field has
apostrophe in it (like Aaron's) because the findfirst
command use apostrophe and Access get confused where to
stop. I tried replacing them with double quote but it
didn't work.
Click to expand...

What did you try replacing? What you need to do is delimit the
FindFirst critrion in " instead of '. Could you post the code?

It should be something like

rs.FindFirst "[SearchField] = """ & strFind & """"

using the convention that two " characters are used to represent a
single " within a string constant delimited by ". Alternatively you
can use the fact that the ASCII value of " is 34:
rs.FindFirst "[SearchField] = " & Chr(34) & strFind & Chr (34)


Either of these will evaluate to

[SearchField] = "Aaron's"

which will work correctly.


.
Sorry. Should have read your email more carefully
Click to expand...
regarding the double double quotes. Problem solved. Thank
you so very much.
 
When you use the double quotes you will have the same problem if you search
for something like 50" Gas Plasma TV (I am currently lusting for one of
those bad boys. Gotta' write more code make more money.) .

I have found that only one size fits all, works every time, way to do this
kind of thing is to create a function that doubles up apostrophe's and run
the search string through it before sending it to the database like:

rs.FindFirst "[SearchField] = '" & FixQuotes(strFind) & "'"

Here is a FixQuotes() function that handles this

Public Function FixQuotes(strQuoted As String) As String
' Purpose Double up single quotes for use in a Sql statements
Dim i As Integer, strOut As String

strOut = ""
For i = 1 To Len(strQuoted)
If Mid(strQuoted, i, 1) = Chr(39) Then
strOut = strOut & Chr(39)
End If
strOut = strOut & Mid(strQuoted, i, 1)
Next
FixQuotes = strOut
End Function

Ron W
John Vinson said:
I am having trouble with search when the search field has
apostrophe in it (like Aaron's) because the findfirst
command use apostrophe and Access get confused where to
stop. I tried replacing them with double quote but it
didn't work.
Click to expand...

What did you try replacing? What you need to do is delimit the
FindFirst critrion in " instead of '. Could you post the code?

It should be something like

rs.FindFirst "[SearchField] = """ & strFind & """"

using the convention that two " characters are used to represent a
single " within a string constant delimited by ". Alternatively you
can use the fact that the ASCII value of " is 34:
rs.FindFirst "[SearchField] = " & Chr(34) & strFind & Chr(34)


Either of these will evaluate to

[SearchField] = "Aaron's"

which will work correctly.
Click to expand...
 
Back
Top