Reports using "between" dates

[Kathy Vander Vlist]

I'm trying to run a report between certain dates. For some reason when I run
them, they do not include the 1st or last date put in. For instance I run a
report between May 1,2007 and May 31, 2007. The report leaves out May 1st and
May 31st.

 

[Rick Brandt]

I'm trying to run a report between certain dates. For some reason
when I run them, they do not include the 1st or last date put in. For
instance I run a report between May 1,2007 and May 31, 2007. The
report leaves out May 1st and May 31st.

Missing May 1st is a mystery (my guess being that there actually are no
records dated May 1st), but missing the last date is a common problem when
the dates in the table have non-midnight time components. After all May
31st, 2007 at 10:00 AM is not between May 1st, 2007 and May31st, 2007
because when you don't specify the time midnight is assumed.

The solution is to add an extra day to the end date, but if there migth be
some records that DO have midnight as the time you could get records for
that last date that you don't want. In that case don't use BETWEEN since
that is inclusive. Instead use something like...

WHERE DateField >= #May 1, 2007#
AND DateField < DateAdd("d", 1, #May 31, 2007#)

The above is a weird looking example, but I'm assuming you are getting your
start and end date values from somewhere besides hard-coding them as I did.

 

[Baz]

How are you running the report?

 

[Kathy Vander Vlist]

What do you mean by running the report?

 

[Kathy Vander Vlist]

Rick,
If I don't want to hard code it is there a way to run it possibly using date
and time and have it come out correctly?

 

[Rick Brandt]

Rick,
If I don't want to hard code it is there a way to run it possibly
using date and time and have it come out correctly?

Not sure I understand the question. How were you supplying the two date
values before? You should be able to do the same thing, except change to >=
for the starting date and < for the end date adding one day as my example
showed.

 

[Kathy Vander Vlist]

Okay, I'm having a brain freeze! Inside the query it reads
Between [Enter the Start Date:] And [Enter the End Date:]
So, what would I put in instead of the above.
Thanks!

 

[Rick Brandt]

Okay, I'm having a brain freeze! Inside the query it reads
Between [Enter the Start Date:] And [Enter the End Date:]
So, what would I put in instead of the above.
Thanks!

=[Enter the Start Date] AND < DateAdd("d", 1, [Enter the End Date])

 

[Kathy Vander Vlist]

Thanks Rick! I'm going to give it a try!
--
K Vander Vlist

Okay, I'm having a brain freeze! Inside the query it reads
Between [Enter the Start Date:] And [Enter the End Date:]
So, what would I put in instead of the above.
Thanks!

=[Enter the Start Date] AND < DateAdd("d", 1, [Enter the End Date])

 

[Kathy Vander Vlist]

Rick,
It works! Now, is there a way that I can still have the date show on the
report as May 1st to May 31st? I sure appreciate your help!
--
K Vander Vlist

Okay, I'm having a brain freeze! Inside the query it reads
Between [Enter the Start Date:] And [Enter the End Date:]
So, what would I put in instead of the above.
Thanks!

=[Enter the Start Date] AND < DateAdd("d", 1, [Enter the End Date])

 

[Rick Brandt]

Rick,
It works! Now, is there a way that I can still have the date show on
the report as May 1st to May 31st? I sure appreciate your help!

You can refer to those same parameter markers in your report as long as you
spell them exactly the same. For example you could have this in a TextBox
ControlSource...

="For records between " & [Enter the Start Date:] & " and " & [Enter the End
Date:]