Search code examples
sqlms-accessvbams-access-2016

MS Access: Compound greater than less than comparisons

The question is simple: Why does this give the wrong answer (0)

IIf(Date()>=#3/16/2018#>=Date()-30,1,0)

While this gives the correct answer (1)

IIf(Date()>=#3/16/2018# AND #3/16/2018#>=Date()-30,1,0)

More specifically, what is Access doing in the first case?

This situation is made even more curious because, when I execute this code, I get an unexpected answer (1)

IIf(Date()<=#3/16/2018#<=Date()-30,1,0)
Solution

  • You can't compound comparisons in Access!

    Using your last example, Access first executes the first comparison:

    Date()<=#3/16/2018#

    That might result in True or False. Let's say it's True

    Then, Access evaluates the second comparison:

    True <= Date() - 30

    (This is since they're processed from left to right, and the first one is true).

    This doesn't make much sense, but Access can cast a boolean to a number (-1 = True, 0 = False), and a date too (for example, today = 43186, since dates are defined as the number of days elapsed since 1899-12-30).

    That means the second comparison results in:

    -1 <= 43186 - 30

    And that's certainly true. You also see that if the first comparison is false, the second one will be true nonetheless. Your comparison will pretty much always return true.