Code snippet - to quote strings - single or double or something else

Status
Not open for further replies.

essaytee

Need a good one-liner.
Local time
Tomorrow, 02:05
Joined
Oct 20, 2008
Messages
531
I always find double quoting very difficult on the eyes and so easy to stuff it up, missing quotes or too many quotes. To make life easier, why not write a little function where your string to be quoted is passed in and the output is a correctly quoted string. I use the following, hope you find it of value.

Code:
Code:
Function Enclose(Optional pStr As Variant = "", Optional pIntType As Integer = 2) As String
    
    ' use this instead of directly writing " " "  or " ' "  or " # " in SQL statements, eases confusion
    ' It will make writing SQL code easier, easier code to view.
    ' Author:   Steven Taylor aka essaytee
    ' Date:     23 Feb 2019
    '
    ' INPUTS    pStr        The string value to be enclosed     (default value is an empty string)
    '           pIntType    0 = Not enclosed
    '                       1 = Single quote '
    '                       2 = Double quote "                  (default value if not passed in)
    '                       3 = Pound/Hash #
    '                       4 = Less/Greater than < >
    '                       5 = Square brackets [ ]
    '                       6 = Curly braces { }
    '                       7 = Parenthesis ( )
    '
    ' OUTPUT    The passed in string (pStr) enclosed in quotes or hash or <> []
    '
    '
    ' NOTES     This simple function could be further developed to account for other enclosing symbols
    '           To find the Chr$ number of symbol, Immediate window eg. ? Asc("<") will give you the number
    '
    ' Sample sql
    ' Dim strSql as string
    ' strSql = "SELECT * FROM tblWhatever WHERE FldName = " & Enclose(Me.txtSearchName) & " AND FldAddress = " & Enclose(Me.txtSearchAddress) & ";"
    ' As written in line above, the string values will be returned enclosed in double quotes.
    '
    ' Optional:     Why does pStr default to an empty string.  Legacy issue for me, I did have functions just returning " or ' or #
    ' Lastly:       In relation to dates I've created another similar function that accounts for the US format, required for SQL, and encloses in #
    '
    '
    
    Dim strEncloseLeft As String
    Dim strEncloseRight As String
            
    Select Case pIntType
        Case 1 ' Single quote - think 1 as single quote
            strEncloseLeft = Chr$(39)
            strEncloseRight = Chr$(39)
        Case 2 ' Double quote - think 2 as two quotes, therefore double quote
            strEncloseLeft = Chr$(34)
            strEncloseRight = Chr$(34)
        Case 3 ' Pound/Hash #
            strEncloseLeft = Chr$(35)
            strEncloseRight = Chr$(35)
        Case 4 ' Less than, greater than   < >
            strEncloseLeft = Chr$(60)
            strEncloseRight = Chr$(62)
        Case 5 ' Square brackets [ ]
            strEncloseLeft = Chr$(91)
            strEncloseRight = Chr$(93)
        Case 6 ' Curly braces { }
            strEncloseLeft = Chr$(123)
            strEncloseRight = Chr$(125)
        Case 7 ' parenthesis ( )
            strEncloseLeft = Chr$(40)
            strEncloseRight = Chr$(41)
    End Select
        
    If pStr = "" Then
        satEnclose = strEncloseLeft
    Else
        satEnclose = strEncloseLeft & pStr & strEncloseRight
    End If
End Function
Obviously, you can change the name of the function, shorten it, whatever, but I'm sure you get the idea of what is going on.

I did post this the other day against another thread but have since thought it may be more useful in its own thread.
 
Last edited:
Not bad, a little work on the front end but I can definitely see practical use for this. Thanks for sharing.
 
Status
Not open for further replies.

Users who are viewing this thread

Back
Top Bottom