Well, let me preface this by saying I hate concatenating strings in VBA.
It's ugly and I usually struggle with the single quote and double quotes at
some point.
Python has multiple ways of string concatenation.. so I sort of drew on one
of their methods that resonated best with me which was f-strings:
Public Function ConcatenateString(ByVal strInput As String, ParamArray
MyArray() As Variant) As Variant
'function replaces {0} {1} etc with parameterized input
Dim i As Integer
Dim strReplace As String
For i = LBound(MyArray) To UBound(MyArray)
'Debug.Print TypeName(MyArray(i))
Select Case TypeName(MyArray(i))
Case "Integer", "Long", "Single", "Double", "Byte", "Decimal",
"Currency"
strReplace = MyArray(i)
Case "String", "Variant", "Date", "Boolean"
strReplace = "'" & MyArray(i) & "'"
End Select
strInput = Replace(strInput, "{" & i & "}", strReplace)
Next
ConcatenateString = strInput
End Function
So in this function we replace {0}{1}{2}.... positions with that same
number in the array input.
So something like this:
?ConcatenateString("SELECT * FROM PETS WHERE Breed = {0} AND Size =
{1}","Dog","Large")
Becomes:
SELECT * FROM PETS WHERE Breed = 'Dog' AND Size = 'Large'
or this:
?ConcatenateString("SELECT * FROM PETS WHERE Height >= {0} AND Breed =
{1}", 12,"Bird")
Becomes:
SELECT * FROM PETS WHERE Height >= 12 AND Breed = 'Bird'
I'm looking for input on gotchas... I thought about breaking the Type
"date" out to add the pound signs before and after, but since I don't
strictly use DAO for queries I just decided to treat it like a string.
The one gotcha I've discovered is something like
"SELECT * FROM {0}" wouldn't work, since it'll add the single quotes around
the parameter.... but this function was written to replace criteria in a
WHERE string.