Intermediate

First, Make It Right (Then Make It Fast)

In queries, nested IIf statements are fast and (relatively) easy to write. The problem is they are nearly impossible to read.

Mike Wolfe

Jun 28, 2021 • 4 min read

When writing a query in Microsoft Access, it's always faster to use native Jet/ACE functions and statements, like IIf and Is Null, rather than calling a custom VBA function. If you are trying to squeeze every last bit of performance out of your queries, then that is the way to go.

But don't get ahead of yourself! Before you start worrying about how fast your query is, you need to make sure it's producing correct results.

Young Me: "Look how fast my query is!"

As a young developer, I was always looking to maximize performance. That meant doing things like avoiding the Nz() function in favor of Jet's native IIf and Is Null statements.

For example, the following two query fields will return identical results, but the second will perform much better than the first:

BasePay + Nz(BonusAmt, 0) AS TotalPay

This requires a relatively slow call to the VBA Nz() function–which returns a Variant type.

BasePay + IIf(BonusAmt Is Null, 0, BonusAmt) AS TotalPay

IIf and Is Null are native Jet statements. This statement will preserve BonusAmt's type.

Now, swapping out IIf and Is Null for the Nz function is something I still regularly do. What I've stopped doing is nesting IIf statements just to avoid calling into VBA.

Older Me: "Look how correct my query is"

Nested IIf statements are easy to write.

The problem is they are nearly impossible to read.

Let's take a look at another example of two query field definitions that return identical results.

The first uses pure Jet/ACE SQL:

IIf([DueDate]-[AsOf]>=30,[OriginalAmt]-[DiscountAmt],
    IIf([AsOf]-[DueDate]>=15,[OriginalAmt]+[PenaltyAmt],
        [OriginalAmt])) AS AmtDue

Nested IIf statements are even harder to read inside the Access query editor because the line breaks get removed on save.

The second performs the same calculation using a VBA function:

CalcAmtDue([DueDate],[OriginalAmt],[DiscountAmt],[PenaltyAmt],[AsOf]) AS CalcDue

There is no question that the second is easier to read. As long as you pass the arguments in the correct order to the function, it's almost impossible to screw it up.

Young Me: "But your function could be wrong!"

Code is always harder to read than it is to write. It doesn't matter if the code is VBA or SQL. So how big an advantage is it really to write the logic in VBA, especially when you know it is going to run slower?

Well, I can think of a few reasons why the logic is more likely to be correct in VBA than in SQL:

VBA can be commented
VBA allows defining constants
VBA preserves whitespace
The same VBA function can be called from multiple queries
And, most importantly, the VBA function can be tested – BOOM! [mic drop]

Older Me: "I can prove the VBA is correct"

Here's the accompanying VBA function. Notice that the function is documented and verified with doc tests. With this approach, I can prove that there are no pesky off-by-one errors, etc. If I come up against a weird, bug-producing edge case in the future, I can:

Document a failing test for the bug
Refactor the function to pass the test
Run the other tests to ensure I didn't introduce any regressions

'>>> CalcAmtDue(#6/30/2020#, 100, 2, 10, #5/31/2020#)
'  98
'>>> CalcAmtDue(#6/30/2020#, 100, 2, 10, #6/1/2020#)
' 100
'>>> CalcAmtDue(#6/30/2020#, 100, 2, 10, #6/30/2020#)
' 100
'>>> CalcAmtDue(#6/30/2020#, 100, 2, 10, #7/14/2020#)
' 100
'>>> CalcAmtDue(#6/30/2020#, 100, 2, 10, #7/15/2020#)
' 110
Function CalcAmtDue(DueDate As Date, OriginalAmt As Currency, _
                    DiscountAmt As Currency, PenaltyAmt As Currency, _
                    Optional CalcAsOf As Date = #12/31/9999#) As Currency
    Const DiscountDays As Long = 30
    Const PenaltyDays As Long = 15
    
    Dim AsOf As Date
    If CalcAsOf = #12/31/9999# Then
        AsOf = Date
    Else
        AsOf = CalcAsOf
    End If
    
    Select Case True
    Case (DueDate - AsOf) >= DiscountDays
        CalcAmtDue = OriginalAmt - DiscountAmt
    Case (AsOf - DueDate) >= PenaltyDays
        CalcAmtDue = OriginalAmt + PenaltyAmt
    Case Else
        CalcAmtDue = OriginalAmt
    End Select
    
End Function

Young Me: "Have fun waiting for your report to open, old man!"

There is no question that calling into a VBA function from a query will severely reduce the query's performance. I'm certainly not advocating for ignoring performance just for the sake of readability. But correctness is always more important than speed.

You can fly from New York to Los Angeles in 6 hours, while it takes me 12 hours to drive from New York to Chicago. That's twice as long to travel only half as far. But if the goal is to watch a Cubs-White Sox game, I'm the only one who'll be able to watch the game in-person.

Older Me: "Sounds like someone's having premature optimizations"

For most relatively complex calculations, you won't be performing them against thousands of records. Oftentimes, you can get acceptable performance simply by aggressively filtering your data so that there are fewer calls to the VBA function.

And, if there truly is a large, set-based operation that needs to be optimized, you can always do that later once it's clearly necessary.

In the meantime, you'll have an application that's easier to write, read, and maintain.

Image by Steve Sewell from Pixabay