The first thing to remember when working with SQL is that you need to create a string value to pass to SQL. Of course, within that string value may also be substrings that are string values to SQL. When you appreciate which is which it makes everything so much easier.
Typically, when I'm creating complex strings that need values inserted into them I use the
Replace()
function. Actually, I use it so much I have a
MultiReplace()
function that I use instead for ease of use. I'll include the code for that below but my illustration will use the inbuilt
Replace()
instead :
- strSQL = "UPDATE [Sheet1] SET [%F]='%V' Where [Vertical]='%C'"
-
strSQL = Replace(strSQL, "%F", Me.Text6)
-
strSQL = Replace(strSQL, "%V", Me.Text47)
-
strSQL = Replace(strSQL, "%C", Me.Combo1)
Notice that the first line shows clearly what it is you're working with and what delimiters are used for each of the values. This is much easier to read and work with than multiple concatenation of literal and other values to create a complex string.
The code for MultiReplace() is :
- 'MultiReplace() takes each pair of parameters from avarArgs() and replaces the
-
' first with the second wherever found in strMain.
-
'Using VbBinaryCompare means that case is recognised and not ignored.
-
'08/05/2013 Updated to support passing of an array directly into avarArgs.
-
Public Function MultiReplace(ByRef strMain As String _
-
, ParamArray avarArgs() As Variant) As String
-
Dim intX As Integer
-
Dim avarVals() As Variant
-
-
'Code to handle avarArgs passed as an existing array.
-
If (UBound(avarArgs) = LBound(avarArgs)) _
-
And IsArray(avarArgs(LBound(avarArgs))) Then
-
ReDim avarVals(LBound(avarArgs) To UBound(avarArgs(LBound(avarArgs))))
-
For intX = LBound(avarVals) To UBound(avarVals)
-
avarVals(intX) = avarArgs(LBound(avarArgs))(intX)
-
Next intX
-
Else
-
avarVals = avarArgs
-
End If
-
If (UBound(avarVals) - LBound(avarVals)) Mod 2 = 0 Then Stop
-
MultiReplace = strMain
-
For intX = LBound(avarVals) To UBound(avarVals) Step 2
-
MultiReplace = Replace(Expression:=MultiReplace, _
-
Find:=Nz(avarVals(intX), ""), _
-
Replace:=Nz(avarVals(intX + 1), ""), _
-
Compare:=vbBinaryCompare)
-
Next intX
-
End Function