How to handle an apostrophe?

[justin.poole]

Software: Access 2002

Problem: Combo box search results in run-time error where the string
being searched has an apostrophe in it (for example: O'Leary).

Anyone have any workarounds for this?

 

[CompGeek78]

A look at the code behind your combo box and which line the error
results on would greatly help anyone trying to find the problem. Did
you create the combo box using the control wizard or did you code the
combo box yourself? More information is necessary.

 

[justin.poole]

Hi,

I used a filter (after update). The code I used:

strSQL = "LKUP_NAME = " & "'" & cboOwner_Search & "'"
Me.Filter = strSQL
Me.FilterOn = True

 

[CompGeek78]

You would probably be better off using a recordset object instead of
using a filter command. The problem is that the SQL string uses the
apostrophe as part of it's syntax, so when it sees the apostrophe in
the name, it sees too many apostrophes to be valid syntax.

Try using this code instead:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LKUP_NAME] = " & Str(Nz(Me![cboOwner_Search], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

 

[Dirk Goldgar]

You would probably be better off using a recordset object instead of
using a filter command. The problem is that the SQL string uses the
apostrophe as part of it's syntax, so when it sees the apostrophe in
the name, it sees too many apostrophes to be valid syntax.

Try using this code instead:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LKUP_NAME] = " & Str(Nz(Me![cboOwner_Search], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

While your summary of the problem is accurate, your code won't work.
There needs to be some kind of quoting to delimit the text value. If
that value won't ever contain a double-quote ("), that character can be
used instead of the single-quote ('):

rs.FindFirst "[LKUP_NAME] = " & _
Chr(34) & Me![cboOwner_Search] & Chr(34)

Note that Chr(34) is the double-quote character.

If there's a chance that the text may contain the double-quote
character, too, you can allow for that by replacing every instance of
that character in the text with a pair of quotes (which will be
understood as one quote character when encountered inside a quoted
string). This approach might look like this:

Dim strQ As String
Dim str2Q As String

strQ = Chr(34)
str2Q = strQ & strQ

rs.FindFirst "[LKUP_NAME] = " & _
strQ & _
Replace(Me![cboOwner_Search], strQ, str2Q) & _
strQ

 

[Steve Schapel]

Justin,

I normally do it like this...
strSQL = "LKUP_NAME = " & """" & cboOwner_Search & """"

 

[Terry Kreft]

But then you have the same problem if the string has embedded double quotes.

The best way is to double up delimiters in the string.

e.g.
strSQL = "LKUP_NAME = '" & Replace(cboOwner_Search, "'", "''") & "'"

or
strSQL = "LKUP_NAME = """ & Replace(cboOwner_Search, """", """""'") &
""""