find criteria doesnt recognize '

[Guest]

anybody know what i am doing wrong with the code below? It doesnt recognize
the apostrophe. If i was to do a search on the title "Joe's Garage", it'll
give me the first match, but the next match it will give me an EOF.

Sub NavByType(Direction)
Dim R As Recordset
Set R = Me.RecordsetClone
R.Bookmark = Me.Bookmark
If Direction = "B" Then
R.FindPrevious "[Title] = '" & Me![TITLE] & "'"
Else
R.FindNext "[TITLE] = '" & Me![TITLE] & "'"
End If
If R.NoMatch Then
MsgBox "No More Matches"
Else
Me.Bookmark = R.Bookmark
End If
End Sub

 

[Douglas J. Steele]

You can use:

R.FindPrevious "[Title] = " & Chr$(34) & Me![TITLE] & Chr$(34)

Chr$(34) is "

However, this won't work if you're looking for something that has a double
quote in it, such as "The "Olde Booke Shoppe"", although that would work
with what you have. Another approach is simply to double up if your
delimiter character appears in the string:

R.FindPrevious "[Title] = '" & Replace(Me![TITLE], "'", "''") & "'"

where that Replace statement is Replace(Me![TITLE], " ' ", " ' ' ")

Of course, for this to work, you have to be using at least Access 2000,
because earlier versions of Access don't have the Replace function.

 

[Guest]

worked beautifully. much appreciated!

You can use:

R.FindPrevious "[Title] = " & Chr$(34) & Me![TITLE] & Chr$(34)

Chr$(34) is "

However, this won't work if you're looking for something that has a double
quote in it, such as "The "Olde Booke Shoppe"", although that would work
with what you have. Another approach is simply to double up if your
delimiter character appears in the string:

R.FindPrevious "[Title] = '" & Replace(Me![TITLE], "'", "''") & "'"

where that Replace statement is Replace(Me![TITLE], " ' ", " ' ' ")

Of course, for this to work, you have to be using at least Access 2000,
because earlier versions of Access don't have the Replace function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

anybody know what i am doing wrong with the code below? It doesnt recognize
the apostrophe. If i was to do a search on the title "Joe's Garage", it'll
give me the first match, but the next match it will give me an EOF.

Sub NavByType(Direction)
Dim R As Recordset
Set R = Me.RecordsetClone
R.Bookmark = Me.Bookmark
If Direction = "B" Then
R.FindPrevious "[Title] = '" & Me![TITLE] & "'"
Else
R.FindNext "[TITLE] = '" & Me![TITLE] & "'"
End If
If R.NoMatch Then
MsgBox "No More Matches"
Else
Me.Bookmark = R.Bookmark
End If
End Sub