find criteria doesnt recognize '

P

procrastinator2k4

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
 
D

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

procrastinator2k4

worked beautifully. much appreciated!

Douglas J. Steele said:
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!)



procrastinator2k4 said:
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
Click to expand...
Click to expand...
 
Top