Apostrophes in search field

[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.

 

[Rick Brandt]

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.

Replace them with two consecutive apostrophes (Aaron''s)

 

[John Vinson]

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.

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.

 

[schlitterbahn]

-----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.

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.


.

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

 

[schlitterbahn]

-----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.

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

regarding the double double quotes. Problem solved. Thank
you so very much.

 

[Ron Weiner]

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

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.

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.