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
Exit_Proc:
On Error Resume Next
Exit Function
Err_Handler:
MsgBox Err.Number & " " & Err.Description, vbCritical, "BuildLike()"
BuildLike = ""
Resume Exit_Proc
End Function
Making Microsoft Access Work. Call (505) 369-1650.