Come Together

I am constantly building dynamic WHERE clauses in my Access projects.  This often involves joining together multiple expressions with " AND ".  If there is a database field with only a few unique values, I may need to build a comma-delimited list to use with an IN clause.

For example, let's say I'm writing a program for an animal control department.  I've built them a report that shows a summary of the calls they responded to.  They can filter the report by start date, end date, and type of animal.  Each of those fields is optional. The animal type is a multi-select list box.

Typical Approach

Here is a common approach to building the WHERE clause for such a report:

Dim Where As String

If Not IsNull(Me.tbStartDate) Then 
    Where = Where & "CallDate >= #" & Me.tbStartDate & "# AND "
End If

If Not IsNull(Me.tbEndDate) Then
    Where = Where & "CallDate <= #" & Me.tbEndDate & "# AND "
End If

Dim Item As Variant, InList As String
For Each Item In Me.lbAnimals.ItemsSelected
    InList = InList & "'" & Me.lbAnimals.ItemData(Item) & "', "
Next Item
If Len(InList) > 0 Then
    'Trim off the trailing ", "
    InList = Left(InList, Len(InList) - 2)
    Where = Where & "AnimalType In (" & InList & ") AND "
End If

If Len(Where) > 0 Then
    'Trim off the trailing " AND "
    Where = Left(Where, Len(Where) - 5)
End If

Problems with the Typical Approach

The above approach is error-prone and sloppy.  The 2 and the 5 are magic numbers.  We could replace them with Len(", ") and Len(" AND "), respectively.  That would be an improvement, but it's cumbersome and more verbose.  We could take it a step further and make ", " and " AND " constants.  That would be cleaner yet, but now it would be getting wordy and hard to read.

Here's a quick rundown of the errors this approach can lead to:

1. Forgetting to trim the trailing string.
2. Trimming the wrong number of characters from the end.
3. Changing the delimiter and forgetting to update the trimming code to match.
4. Forgetting to check for empty string before trimming.

I used this method many times when I started writing VBA in Access.  I made each of the errors I listed above on multiple occasions.  I knew there had to be a better way, so I created a simple function to address the above issues.

Conc() function to the Rescue

I call my function Conc(), which is short for "concatenate."  I tend to use longer names for one-off functions, but for utility functions that will be used regularly, I go with shorter names.  There is a tradeoff in readability.  Long names are more explicit; that's better for infrequently used procedures.  Short names are less distracting; I prefer those for functions I use a lot.  This is one of my top ten most used functions, hence the short name.

I originally wrote this function to create lists for the IN statement.  That's why the Delimiter parameter is optional and it defaults to a comma and space: ", ".

Let's rewrite the above code using this function.  As part of my rewrite, I also cleaned up the quoting and date wrapping using a couple of other well-worn functions, Dt() and Qt():

Dim Where As String

If Not IsNull(Me.tbStartDate) Then 
    Where = Conc(Where, "CallDate >= " & Dt(Me.tbStartDate), " AND ")    
End If

If Not IsNull(Me.tbEndDate) Then
    Where = Conc(Where, "CallDate <= " & Dt(Me.tbEndDate), " AND ")
End If

Dim Item As Variant, InList As String
For Each Item In Me.lbAnimals.ItemsSelected
    InList = Conc(InList, Qt(Me.lbAnimals.ItemData(Item)))
Next Item
If Len(InList) > 0 Then
    Where = Conc(Where, "AnimalType In (" & InList & ")", " AND ")
End If

Source Code

NOTE: The comment lines that begin with '>>> represent doc tests.

'---------------------------------------------------------------------------------------
' Procedure : Conc
' Author    : Mike
' Date      : 1/23/2009 - 4/1/2015
' Purpose   : Concatenates two strings
' Notes     : Eliminates the need to strip off the leading/trailing delimiter when
'               building a string list
' 4/17/09   - If StartText is filled, but nextval is empty, then StartText is returned unchanged.
' 5/ 1/09   - Changed return type of conc from Variant to String.
' 4/ 1/15   - Allow passing Nulls as StartText.
'>>> Conc("1, 2, 3", "4")
' 1, 2, 3, 4
'>>> Conc("This", "that", " and ")
' This and that
'>>> Conc("Five", Null, " and ")
' Five
'>>> Conc(Null, "Dime", " and ")
' Dime
'>>> "#" & Conc(Null, Null) & "#"
' ##
'---------------------------------------------------------------------------------------
'
Function Conc(StartText As Variant, NextVal As Variant, _
              Optional Delimiter As String = ", ") As String
    If Len(Nz(StartText)) = 0 Then
        Conc = Nz(NextVal)
    ElseIf Len(Nz(NextVal)) = 0 Then
        Conc = StartText
    Else
        Conc = StartText & Delimiter & NextVal
    End If
End Function

Image by analogicus from Pixabay