Dealing with apostrophes

  • Thread starter Thread starter J.C.
  • Start date Start date
J

J.C.

I am using the following module to locate records:

---------------------------------------------------
Private Sub CFRecordLocatorCBP1_AfterUpdate()
' Find the record that matches the control.

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CF_Name] = '" & _
Me![CFRecordLocatorCBP1] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub
---------------------------------------------------

It works well as long as there is no apostrophe in the Me!
[CFRecordLocatorCBP1] string. For obvious reasons, it
fails if there is one.

Has anyone discovered an easy/simple workaround?

jc
 
J.C. said:
I am using the following module to locate records:

---------------------------------------------------
Private Sub CFRecordLocatorCBP1_AfterUpdate()
' Find the record that matches the control.

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CF_Name] = '" & _
Me![CFRecordLocatorCBP1] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub
---------------------------------------------------

It works well as long as there is no apostrophe in the Me!
[CFRecordLocatorCBP1] string. For obvious reasons, it
fails if there is one.

Has anyone discovered an easy/simple workaround?
Click to expand...

If the string to be sought won't contain a double-quote, use that as the
string delimiter inside the SQL statement:

rs.FindFirst "[CF_Name] = " & _
Chr(34) & Me![CFRecordLocatorCBP1] & Chr(34)

If the string to be sought may contain a double-quote, use the Replace()
function to double it up:

rs.FindFirst "[CF_Name] = " & _
Chr(34) & _
Replace(Me![CFRecordLocatorCBP1], """", """""") & _
Chr(34)
 
-----Original Message-----
J.C. said:
I am using the following module to locate records:

---------------------------------------------------
Private Sub CFRecordLocatorCBP1_AfterUpdate()
' Find the record that matches the control.

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CF_Name] = '" & _
Me![CFRecordLocatorCBP1] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub
---------------------------------------------------

It works well as long as there is no apostrophe in the Me!
[CFRecordLocatorCBP1] string. For obvious reasons, it
fails if there is one.

Has anyone discovered an easy/simple workaround?
Click to expand...

If the string to be sought won't contain a double-quote, use that as the
string delimiter inside the SQL statement:

rs.FindFirst "[CF_Name] = " & _
Chr(34) & Me![CFRecordLocatorCBP1] & Chr(34)

If the string to be sought may contain a double-quote, use the Replace()
function to double it up:

rs.FindFirst "[CF_Name] = " & _
Chr(34) & _
Replace(Me![CFRecordLocatorCBP1], """", """""") & _
Chr(34)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...

Dirk -

Worked great. Thanks!

jc
 
Back
Top