[AccessD] String replacement function

Ryan W wrwehler at gmail.com
Tue Aug 24 09:07:31 CDT 2021

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",
                strReplace = MyArray(i)
            Case "String", "Variant", "Date", "Boolean"
                strReplace = "'" & MyArray(i) & "'"
        End Select
        strInput = Replace(strInput, "{" & i & "}", strReplace)

    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 =

SELECT * FROM PETS WHERE Breed = 'Dog' AND Size = 'Large'

or this:
?ConcatenateString("SELECT * FROM PETS WHERE Height >= {0} AND Breed =
{1}", 12,"Bird")


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.

More information about the AccessD mailing list