2 Ways to Escape Leading Quotes when Adding Items to a List Box
Leading quotation marks can cause problems when adding items to a list box or combo box. Here are 2 easy ways to deal with the issue.
In my article, Escaping Semicolons in a Multi-Column Access List Box, I used the visually-identical Greek question mark character to display the semicolon without having it trigger a new column in the control.
In the comments, Jack Stockton offered a related challenge:
How would you handle two other edge cases; adding a single quote or a double quote as a leading character?
This strikes me as a more common edge case than the embedded semicolons. Let's dive in.
Demonstrating the Problem
Users enter data in two text boxes then click the Submit button to append the two values as a new row in the list box control:
If the user enters a leading single or double quote, then Access assumes the whole string is being quoted and the feature breaks:
If there is a pair of matching quotes, the feature breaks in a different way:
Workaround A: Smart Quote Replacement
My first workaround is very similar to the semicolon approach: replace the quote character with a visually similar character.
In this case, that would be smart quote characters:
ChrW(&H2018) ‘: Left single quotation markChrW(&H2019) ’: Right single quotation markChrW(&H201C) “: Left double quotation markChrW(&H201D) ”: Right double quotation mark
Here's the sample code. Note that I'm using the Count parameter of the Replace function to replace only the first leading quote character:
Private Function ReplaceLeadingQuote(Text As String) As String
Select Case Left(LTrim$(Text), 1)
Case "'"
ReplaceLeadingQuote = Replace(Text, "'", ChrW(&H2018), 1, 1)
Case """"
ReplaceLeadingQuote = Replace(Text, """", ChrW(&H201C), 1, 1)
Case Else
'Nothing to escape
ReplaceLeadingQuote = Text
End Select
End FunctionHere's what it looks like in action:
While the smart quotes are visually similar to the plain quotes, they are not visually identical. I could also make a case for using the Right single quotation mark instead of the Left when replacing a leading quote mark, since it will often be used in a way where that makes more sense grammatically (such as in this example).
Ultimately, I don't like this approach. We can do better.
Workaround B: Hidden Control Characters
Now this is a slick workaround.
I've warned about clever code in the past, so let's call this code elegant instead of clever. Since Access is only concerned with whether the first non-space character is a quote, we simply have to provide some other non-space character to sit in front of our user strings. There are several non-printing characters to choose from in the ASCII character table, but I found that the File Separator character fills the need quite nicely:
Private Function EscapeLeadingQuote(Text As String) As String
Const FileSeparatorCharCode As Long = 28
Dim FileSepChar As String
FileSepChar = Chr(FileSeparatorCharCode)
Select Case Left(LTrim$(Text), 1)
Case "'"
EscapeLeadingQuote = Replace(Text, "'", FileSepChar & "'", 1, 1)
Case """"
EscapeLeadingQuote = Replace(Text, """", FileSepChar & """", 1, 1)
Case Else
'Nothing to escape
EscapeLeadingQuote = Text
End Select
End FunctionHere it is in action. The quotes are preserved, they don't look any different, and the file separator character did not contribute any leading whitespace to our values. I call that a win!
Keep in mind, though, that the file separator character does occupy a space in the string. In other words, the top left cell of the list box–'92– has a length of 4: the three visible characters, plus the leading file separator character (Chr(28)). You'll want to strip those out before you use those values.
Full Code Sample
Here is the full code sample from the form. It includes the code to escape and unescape text on the way in and out of list boxes (and combo boxes) with a Row Source Type of "Value List."
Private Sub btnSubmit_Click()
Me.List5.AddItem EscapeForListOrCombo(Me.Text0.Value) & ";" & _
EscapeForListOrCombo(Me.Text2.Value)
End Sub
Private Function EscapeForListOrCombo(Text As String) As String
Dim s As String
s = Text
s = EscapeSemiColon(s)
's = ReplaceLeadingQuote(s)
s = EscapeLeadingQuote(s)
EscapeForListOrCombo = s
End Function
Private Function ReplaceLeadingQuote(Text As String) As String
Select Case Left(LTrim$(Text), 1)
Case "'"
ReplaceLeadingQuote = Replace(Text, "'", ChrW(&H2018), 1, 1)
Case """"
ReplaceLeadingQuote = Replace(Text, """", ChrW(&H201C), 1, 1)
Case Else
'Nothing to escape
ReplaceLeadingQuote = Text
End Select
End Function
Private Function EscapeLeadingQuote(Text As String) As String
Const FileSeparatorCharCode As Long = 28
Dim FileSepChar As String
FileSepChar = Chr(FileSeparatorCharCode)
Select Case Left(LTrim$(Text), 1)
Case "'"
EscapeLeadingQuote = Replace(Text, "'", FileSepChar & "'", 1, 1)
Case """"
EscapeLeadingQuote = Replace(Text, """", FileSepChar & """", 1, 1)
Case Else
'Nothing to escape
EscapeLeadingQuote = Text
End Select
End Function
Private Function EscapeSemiColon(Text As String) As String
'Replace embedded semicolons with the visually-indistinguishable
' Greek question mark character (U+037E)
'https://en.wikipedia.org/wiki/Question_mark#Greek_question_mark
EscapeSemiColon = Replace(Text, ";", ChrW(&H37E))
End Function
Function UnescapeFromListOrCombo(Text As String) As String
Dim s As String
s = Text
s = UnescapeSemiColon(s)
s = UnescapeLeadingQuote(s)
UnescapeFromListOrCombo = s
End Function
Private Function UnescapeSemiColon(Text As String) As String
UnescapeSemiColon = Replace(Text, ChrW(&H37E), ";")
End Function
Private Function UnescapeLeadingQuote(Text As String) As String
UnescapeLeadingQuote = Replace(Text, Chr(28), "", 1, 1)
End Function
Private Sub Form_Unload(Cancel As Integer)
'Print the loaded items to the Immediate Window
Dim i As Integer
For i = 0 To Me.List5.ListCount - 1
Debug.Print UnescapeFromListOrCombo(Me.List5.Column(0, i)), _
UnescapeFromListOrCombo(Me.List5.Column(1, i))
Next i
End SubReferenced articles
Mike Wolfe
Mike Wolfe
Image by mohamed Hassan from Pixabay
