How do I use the Between . . . And Operator?

[Guest]

I have setup a query to find all of the records between 01/01 and 12/31 with
the Between . . . And Operator. This Operator is supposed to be inclusive,
but when I run the query, it doesn't include records from 12/31. (It does
include records from 01/01.) This is what I have entered in the Criteria
field:
Between [Type the beginning date MM/DD:] And [Type the ending date MM/DD:]

Is this a bug or am I doing something wrong? Thanks!

 

[[MVP] S.Clark]

When the year of a date is omiitted, then the current year is used.

So, if you're using this method of entry hoping to get 12/31/05, then you
can see that there is problem.

My suggestions are:
1. Capture the Year in the entry.
2. Explicitly declare these parameters as Dates in the Query / Parameter's
box.

 

[Guest]

The problem I see is that you're requesting data for a time period that
hasn't occurred yet (i.e., 12/31). In other words, consider trying Between
12/31
and 1/1. If that doesn't work, perhaps you should enter the year (i.e.,
12/31/2005) in your parameter too.

I'm not an expert, but I hope this tidbit helps.

 

[fredg]

I have setup a query to find all of the records between 01/01 and 12/31 with
the Between . . . And Operator. This Operator is supposed to be inclusive,
but when I run the query, it doesn't include records from 12/31. (It does
include records from 01/01.) This is what I have entered in the Criteria
field:
Between [Type the beginning date MM/DD:] And [Type the ending date MM/DD:]

Is this a bug or am I doing something wrong? Thanks!

Of what year? 2005? 2006?
You need to include the year with the parameter prompt.

As written, Access will assume the year to be 2006, the current year.
If you enter 1/1/2005 and 12/31/2005 you should get all of the year
2005 .... IF..... the date field does NOT include a time value.

If your Date Field DOES include a Time value, any data past 00:00:00
AM of the [EndDate] is not within the Between [FromDate] And [EndDate]
criteria.
Easiest work-around is to just add one day manually to the [EndDate]
when prompted, i.e. instead of entering 12/31/2005 enter 1/1/2006.

A better work-around would be to set
[Type the beginning date MM/DD/YYYY:]
and
[Type the ending date MM/DD/YYYY:]
and their expected Datatype as query parameters in the Query Parameter
Dialog (Click Query + Parameter).
Then change your criteria to:
Between [Type the beginning date MM/DD/YYYY:] and ([Type the ending
date MM/DD/YYYY:] + 1)

You can then enter 1/1/2005 and 12/31/2005 for all records through
that date.

Note: I have added YYYY to the parameter prompts above.