UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> ConvertQuote    

The inbuilt Replace() function isn't available in versions of Access prior to A2000, so the following function will handle single quotes for SQL purposes. For versions of Access from A2000 upwards, the Replace() function can be used


For Access versions prior to 2000:

CODE
' Code courtesy of UtterAccess Wiki
' http://www.utteraccess.com/wiki/index.php/Category:FunctionLibrary
' Original submission by John White (jwhite)
' Date contributed: 08/26/2010
'
' DESCRIPTION
' Procedure to convert a string with single quote characters to a string with delimited single
' quote characters. Such must be done to strings before appended or updating fields with
' for proper SQL syntax.

Public Function ConvertQuote(strInput As String) As String

'* Note that it calls a ErrorHanlder function to process any error that occurs - not
'* included in the example. MODULE_NAME is the Constant that is set at the top of the
'* code module that I pulled this function from.

   On Error GoTo ConvertQuote_Error
   
   Dim strTemp As String
   Dim intTemp As Integer, i As Integer

   If InStr(1, strInput, "'") > 0 Then
       intTemp = Len(strInput)
       For i = 1 To intTemp
           If Mid$(strInput, i, 1) = "'" Then
               'Convert one single quote (') to two ('')
               strTemp = strTemp & "''"
           Else
               strTemp = strTemp & Mid$(strInput, i, 1)
           End If
       Next i
       
       ConvertQuote = strTemp
   Else
       ConvertQuote = strInput
   End If

ConvertQuote_Cleanup:
   Exit Function

ConvertQuote_Error:
   ErrorHandler MODULE_NAME, "ConvertQuote"
   Resume ConvertQuote_Cleanup
End Function


For Access versions 2000 onwards:

CODE
Public Function ConvertQuote(strInput As String) As String

  ConvertQuote = Replace(strInput,"'","''")

End Function


Creative Commons License
ConvertQuote by UtterAccess Wiki is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
Editing and revision of the content is freely encouraged; for details, see Expected Usage.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 5,572 times.  This page was last modified 09:32, 6 April 2011 by Jack Leach. Contributions by Alan Greenwood and jwhite  Disclaimers