Dealing With Quotes in Text Fields

The following tutorial is taken from the internet when I started working with my Library System using MS Access.

I googled this topic to find the author but I can't find him.

This tutorial will handle a text field that contain a value with apostrophe, single or double quotation.

I added it here so other programmer will benefit from this code. You can use this in visual basic or MS Access program.

When building an sql statement that contains text fields it usually happens

that one or more fields ends up containing a single or double quotation

mark. This can cause the statement to fail. For example, consider:

strSQL="Select CustName,CustID From CustTable 

     Where CustName = '" & strName & "'"

If the strName variable contains plain text like "Acme" then the statement passed to the database engine reads "...CustName = 'Acme'" and it works fine. If the customer name is "John's Market" the database engine sees "...CustName = 'John's Market' " and has no idea how to handle the stuff after the closing quote it sees after "John".

There are three ways around this:

1. Translate all single quotes to some other character and/or disallow them

2. Use double quotes instead of single quotes. The chance of a text field containing double quotes is usually pretty slim and they can be changed or disallowed if necessary

3. Double the single quotes in the field -- the data base engine should correctly recognize doubled quotes as indicating a single quote in the data and store it as such.

The following function takes a text field and checks it for quotes. If no single quotes are found it returns it surrounded by single quotes. If any single quotes are found but no double quotes it surrounds it with double quotes. If both characters exist it doubles the single quotes and uses single quotes around it:

 
Function QuotedText(ByVal strText As String) As String    
   Dim strOut As String    
   Dim x As Integer
 
   If InStr(strText, "'") = 0 Then
      ' no single quote found -- use them
 
      strOut = "'" & strText & "'"
   ElseIf InStr(strText, Chr$(34)) = 0 Then
      'single but no double -- use doubles
 
      strOut = Chr$(34) &amp; strText &amp; Chr$(34)</p>
   Else
      ' double-up single quotes
      x = InStr(strText, "'")
 
      Do While x
         strOut = Left$(strText, x) &amp; "'" &amp; Mid$(strText, x + 1)
 
         strText = strOut
 
         x = InStr(x + 2, strText, "'")
      Loop
   strOut = "'" &amp; strText &amp; "'"
   End If
   ' the DB engine also dislikes pipe characters so I lose them...
   x = InStr(strText, "|")
   Do While x > 0
      Mid$(strOut, x, 1) = "!"
      x = InStr(strOut, "|")
   Loop
   QuotedText = strOut
End Function

To use it, do something like:

strSQL="Select CustName,CustID From CustTable 
     Where CustName = " & QuotedText(strName)

     strName              output
     Acme                    'Acme'
     John's Market     "John's Market"
     Jay's "Bistro"       'Jay''s "Bistro"'