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:
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.
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
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: