> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Beware the BETWEEN
- URL: https://nolongerset.com/beware-the-between/
- Published: 2021-10-14T03:42:17.000Z
- Updated: 2026-05-08T13:01:53.000Z
- Description: Using the BETWEEN clause with date-time fields may lead to unexpected results. There's a safer alternative.
- Author: Mike Wolfe
- Tags: Basic, SQL, #Import 2026-05-20 02:59

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()](https://nolongerset.com/previewreport-function/)* function and hard-coded dates for demonstration purposes*):

```vba
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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/10/image-40.png)

### Dates With Times? You're Not So Good

But what if your dates look like this:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/10/image-39.png)

### 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:

```vba
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:

```vba
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](https://nolongerset.com/quoth-thy-sql-evermore/)**.

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

There are four key points to remember:

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

---

### Referenced articles

[PreviewReport FunctionThis custom function is the simplest and safest way to preview reports in any Microsoft Access application.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/03/award-3961319_1920.jpg)](https://nolongerset.com/previewreport-function/)

[Quoth thy SQL? Evermore!Solving the “O’Malley problem” with dedicated functions to sanitize our strings. Little Bobby Tables would be proud.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2020/09/crow-3347677_1920-1.jpg)](https://nolongerset.com/quoth-thy-sql-evermore/)

*Image by [Vlad Vasnetsov](https://pixabay.com/users/vladvictoria-9785604/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=4597886) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=4597886)*