Basic

Beware the BETWEEN

Using the BETWEEN clause with date-time fields may lead to unexpected results. There's a safer alternative.

Mike Wolfe

Oct 13, 2021 • 3 min read

What's October without Halloween?

If you're not careful about how you set up the date-based criteria on your queries, you may be accidentally cutting Halloween out from the month of October.

Let me explain.

Dealing with Dates can be Dangerous

A common scenario when running reports is to filter them between two dates.

For example, let's say you want to run a report showing all the orders placed during the month of October. Here's how you might preview such a report (using my PreviewReport() function and hard-coded dates for demonstration purposes):

PreviewReport "OrdersByDate", _
              "OrderDate BETWEEN #10/1/2021# AND #10/31/2021#"

Dates Only, No Times? You're Good

This is not a problem if your order dates look like this:

Dates With Times? You're Not So Good

But what if your dates look like this:

I'm Confused...Can You Clarify A Bit?

It may help to remember that every date in VBA and Access includes a time component. If you don't explicitly provide a time component, then midnight is used by default. The default formatting hides this information, but it's always there.

Thus, the following VBA code is equivalent to what I wrote earlier:

PreviewReport "OrdersByDate", _
              "OrderDate BETWEEN #10/1/2021 12:00:00 AM# " & _
              "              AND #10/31/2021 12:00:00 AM#

When we explicitly include the implied time portion in our hardcoded dates, it's much clearer that the date #10/31/2021 8:32:54 AM# falls outside of our BETWEEN criteria.

A Safer Alternative

Let's rewrite the WHERE condition so that it produces the correct results in both scenarios:

PreviewReport "OrdersByDate", _
              "OrderDate >= #10/1/2021# AND " & _
              "OrderDate < #11/1/2021#"

With this approach, we avoid the problem of excluding every order placed during the day on October 31st.

The Generalized Version

Here's what the code might look like if we replace the hardcoded dates with variables and my Dt() function.

PreviewReport "OrdersByDate", _
              "OrderDate >= " & Dt(StartDate) & " AND " & _
              "OrderDate < " & Dt(EndDate + 1)

There are four key points to remember:

Use a greater than or equal (>=) operator with the StartDate
Use a less than (<) operator with the EndDate
Add a day to the user-supplied EndDate
Ensure the StartDate and EndDate variables do not include a time portion

Referenced articles

Image by Vlad Vasnetsov from Pixabay