505.369.1650 [email protected]

Microsoft Access SQL provides a Like operator that can be used in queries to compare values to a pattern.  For example, let’s say that you have an Employee table with FirstName and Middle fields, and you would like to find all records that have “Bob” or “Robert” (or variations of those) in either of those two fields.  The VBA function below will automatically build the Like statement that you would use.

Calling the function in the Immediate window:

?BuildLike("Employees","FirstName, Middle","Bob, Robert")

Will result in this:

((([Employees].[FirstName]) Like '*Bob*' Or ([Employees].[FirstName]) Like '*Robert*')) 
OR ((([Employees].[Middle]) Like '*Bob*' Or ([Employees].[Middle]) Like '*Robert*'))

You can take the results and add them in the WHERE section of a query in SQL view.  Or call the function in a procedure that is building a complete SQL string on the fly.  The ValueList argument could be a comma-delimited list created by selections made in a list box.

This function uses the * wildcard in front and behind the values, so it will produce records where any part of the field contains the value.

Public Function BuildLike(TableName As String, FieldList As String, _
    ValueList As String) As String
' This procedure passes back a Like statement.
' FieldList is a comma separated list of fields in TableName.
' ValueList is a comma separated list of values that will be in the Like statements.
' BuildLike() Version 1.0.0
' Copyright © 2023 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected].
' Use (at your own risk) and modify freely as long as proper credit is given.
On Error GoTo Err_Handler

    Dim strLike As String
    Dim avarFieldList
    Dim avarValueList
    Dim intElem1 As Integer
    Dim intElem2 As Integer
    Dim strField As String
    Dim strValue As String
    Dim strOR As String
    ' Create an array from FieldList.
    avarFieldList = Split(FieldList, ",")
    ' Create an array from ValueList.
    avarValueList = Split(ValueList, ",")
    strLike = ""
    ' Loop though the fields.
    For intElem1 = 0 To UBound(avarFieldList)
        strField = "[" & TableName & "].[" & Trim(avarFieldList(intElem1)) & "]"
        strOR = "(("
        ' Loop through the values.
        For intElem2 = 0 To UBound(avarValueList)
            strValue = Trim(avarValueList(intElem2))
            strOR = strOR & "(" & strField & ") Like '*" & strValue & "*' Or "
        Next intElem2
        strOR = Left(strOR, Len(strOR) - 4) & "))"
        strLike = strLike & strOR & " OR "
    Next intElem1
    strLike = Left(strLike, Len(strLike) - 4)

    BuildLike = strLike

    On Error Resume Next
    Exit Function
    MsgBox Err.Number & " " & Err.Description, vbCritical, "BuildLike()"
    BuildLike = ""
    Resume Exit_Proc
End Function