asked on

Is There a Way to Deal with apostrophe data in Access VB?

I have an existing table containing a field called "Song Title". I want to allow the user to select a song listed in the combo box to select that record. LOTS of song titles contain the apostrophe character which interferes with VB code. Two proposed solutions are: 1) don't use apostrophe in your data (too late); and 2) replace all apostrophes with double apostrophes (visually unacceptable).

What else can I do?

Rey Obrero (Capricorn1)

<LOTS of song titles contain the apostrophe character which interferes with VB code>
you are probably not using the correct syntax for handling the apostrophe
one way is to wrap the Title of the song with Chr(34)
try this

dim strSong as string
strSong=" & chr(34) & Me.comboSongs & Chr(34) & "

the other way is, replacing the apostrophe with two apostrophes

strSong='" & replace(me.comboSongs, "'","''") & "'

my preference is the first one using Chr(34)

JesterToo

The solution for this will be to change your code slightly... but we would need to see that code to suggest what to change.

ste5an

Well, where does it interfere with your code? And much more interesting: do you see its consequences?? SQL Injection.

Thus never trust user input and use in you code always escaped input.

I use two simple functions in a module:

Public Function SqlQuote(AString As String, Optional ADelimiter As String = "'") As String

  SqlQuote = ADelimiter & Replace(AString, ADelimiter, ADelimiter & ADelimiter) & ADelimiter

End Function

Public Function SqlQuoteNull(AString As Variant, Optional ADelimiter As String = "'") As String

  If Len(Trim(Nz(AString & "", ""))) = 0 Then
    SqlQuoteNull = "NULL"
  Else
    SqlQuoteNull = ADelimiter & Replace(AString, ADelimiter, ADelimiter & ADelimiter) & ADelimiter
  End If

End Function

Open in new window

Mike Long

ASKER

The "offending code is generated by the Combo Box Wizard - namely:

Private Sub Combo129_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Song Title] = '" & Me![Combo129] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Long

ASKER

I get Runtime Error 3077 because the apostrophe in the data serves as a delimiter

Rey Obrero (Capricorn1)

@mike
did you read my post?

Mike Long

ASKER

Thanks Rey,

Geez you guys can solve problems faster that I can read.

ste5an

E.g.

Private Sub Combo129_AfterUpdate()
     ' Find the record that matches the control.
     Dim rs As Object

     Set rs = Me.Recordset.Clone
     rs.FindFirst "[Song Title] = " & SqlQuote(Me![Combo129])
     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
 End Sub

Open in new window

Mike Long

ASKER

Have I correctly marked this question "answered"? And have I correctly acknowledged Rey Obrero as the expert whose suggestion worked for me?

Mike Long

ASKER

Thanks.
WTF??
I've clicked "Best Solution". But it keeps turning off.
The EE UI keeps changing. I used to use EE regularly 15 years ago or so, but I never had this much trouble closing a question. I don't see what I'm meant to do....

Jim Dettman (EE MVE)

I see that you got it.

<<The EE UI keeps changing. I used to use EE regularly 15 years ago or so, but I never had this much trouble closing a question.>>

They keep trying to improve things because the #1 problem is people not closing questions.

Jim.