Using Between...And with General Date

[mewins]

I am trying to query all records created between given dates. The field,
DtRecCrt, is a general date field that is set to =Now() whenever the records
are created. However, when I run the query and have the criteria for
DtRecCrt set to "between [start date] and [end date]," it will return records
up to but not including the end date specified. How do I make the query
include records created on the end date? Thanks

 

[fredg]

I am trying to query all records created between given dates. The field,
DtRecCrt, is a general date field that is set to =Now() whenever the records
are created. However, when I run the query and have the criteria for
DtRecCrt set to "between [start date] and [end date]," it will return records
up to but not including the end date specified. How do I make the query
include records created on the end date? Thanks

Your Date Field includes a Time value.
Therefore 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 11/24/2005 enter 11/25/2005.

A better work-around would be to set
[FromDate]
and
[EndDate]
and their expected Datatype as query parameters in the Query Parameter
Dialog (Click Query + Parameter).
Then change your criteria to:
Between [FromDate] and ([EndDate] + 1)

The best solution, if the Time of day is not necessary, is to run an
Update Query to remove the time value from the date:

Update YourTable Set YourTable.[DateField] = DateValue([DateField]);

Then make sure the time is not included in any new entries, i.e. use
Date() instead of Now().

 

[Tracy]

Hello,

Try using this as your criteria -

=#1/1/2010# And <=Now()

Put your start date between the '#' signs. That's how I search in my
database and it includes the start date I selected and any records that have
the current date on it.

I hope that helps!

I am trying to query all records created between given dates. The field,
DtRecCrt, is a general date field that is set to =Now() whenever the records
are created. However, when I run the query and have the criteria for
DtRecCrt set to "between [start date] and [end date]," it will return records
up to but not including the end date specified. How do I make the query
include records created on the end date? Thanks

 

[Marshall Barton]

I am trying to query all records created between given dates. The field,
DtRecCrt, is a general date field that is set to =Now() whenever the records
are created. However, when I run the query and have the criteria for
DtRecCrt set to "between [start date] and [end date]," it will return records
up to but not including the end date specified. How do I make the query
include records created on the end date?

That's to be expected because the time part from Now is
after the time part (midnight) in [end date].

To deal with that change your criteria to:

=[start date] And <DateAdd("d", 1, [end date])