Report Date Ranges

[Guest]

I have a report that I have created with a date ranges specified in a form.
The form and report work fine. However, my date ranges are not always
picking up the last day of the month. For example, when I select the month
of October, it leaves of the data from the 31st. Here is my where code, any
suggestions?

WHERE (((Transactions.TicketNum)>="0") AND
((Transactions.Transactions_DateTimeTransaction) Between
[Forms]![frmPlantRecapReportSetup]![BeginDate] And
[Forms]![frmPlantRecapReportSetup]![EndDate]) AND ((Transactions.Jobs_Job_ID)
Like nz([Forms]![frmPlantRecapReportSetup]![JobNumber],"*")) AND
((Transactions.VoidTkt)=No) AND ((Transactions.Reprinted)=No) AND
((Transactions.Simulated)=No And (Transactions.Simulated)=Yes))
ORDER BY Transactions.TicketNum;

 

[Ross]

Joe, Try using >= "Start Date" and <="End Date"

works for me.
Ross

 

[Rob Parker]

Hi Joe,

If your dates have a time component (for example, they are entered via a
Now() expression), the last day will be excluded, because the time component
(the decimal portion of your date/time number) will cause the date/time to
not be between your start date and midnight (12:00 AM) of the end date. If
that's the problem, you can add 1 to your EndDate, to force all times on
that date to be included (ie. to go to the "end" midnight of that date).

Your criteria clause would be:
.... Between [Forms]![frmPlantRecapReportSetup]![BeginDate] And
[Forms]![frmPlantRecapReportSetup]![EndDate] + 1) AND ...

HTH,

Rob

 

[onedaywhen]

Hi Joe,

If your dates have a time component (for example, they are entered via a
Now() expression), the last day will be excluded, because the time component
(the decimal portion of your date/timenumber) will cause the date/time to
not be between your start dateand midnight (12:00 AM) of the end date. If
that's the problem, you can add 1 to your EndDate, to force all times on
that dateto be included (ie. to go to the "end" midnight of that date).

Your criteria clause would be:
... Between [Forms]![frmPlantRecapReportSetup]![BeginDate] And
[Forms]![frmPlantRecapReportSetup]![EndDate] + 1) AND ...

I always recommend using temporal functionality on DATETIME values
rather than integer arithmetic and anyhow DATEADD('D', 1, EndDate) is
more explicit. However you add one day, you will also include values
from the *next* day (presumably unwanted) using BETWEEN because it is
inclusive of both values.

Here's a suggested way of using temporal functionality to get
inclusive start- and end dates of the month for a given date e.g. for
the current timestamp (NOW):

SELECT DATEADD('M', DATEDIFF('M', #1990-01-01 00:00:00#, NOW()),
#1990-01-01 00:00:00#) AS current_month_start_date,
DATEADD('M', DATEDIFF('M', #1990-01-01 00:00:00#, NOW()), #1990-01-31
23:59:59#) AS current_month_end_date

Jamie.

--

 

[Rob Parker]

Thanks Jamie,

I appreciate the finer points, and maybe the OP will too. OTOH, my
suggestion will work just fine if his dates include a time component - after
all, he'll only get values from the next day when the time component is
exactly midnight (or as close to midnight as the limits of Access's time
values allow).

Rob

Hi Joe,

If your dates have a time component (for example, they are entered via a
Now() expression), the last day will be excluded, because the time
component
(the decimal portion of your date/timenumber) will cause the date/time to
not be between your start dateand midnight (12:00 AM) of the end date.
If
that's the problem, you can add 1 to your EndDate, to force all times on
that dateto be included (ie. to go to the "end" midnight of that date).

Your criteria clause would be:
... Between [Forms]![frmPlantRecapReportSetup]![BeginDate] And
[Forms]![frmPlantRecapReportSetup]![EndDate] + 1) AND ...

I always recommend using temporal functionality on DATETIME values
rather than integer arithmetic and anyhow DATEADD('D', 1, EndDate) is
more explicit. However you add one day, you will also include values
from the *next* day (presumably unwanted) using BETWEEN because it is
inclusive of both values.

Here's a suggested way of using temporal functionality to get
inclusive start- and end dates of the month for a given date e.g. for
the current timestamp (NOW):

SELECT DATEADD('M', DATEDIFF('M', #1990-01-01 00:00:00#, NOW()),
#1990-01-01 00:00:00#) AS current_month_start_date,
DATEADD('M', DATEDIFF('M', #1990-01-01 00:00:00#, NOW()), #1990-01-31
23:59:59#) AS current_month_end_date

Jamie.

 

[onedaywhen]

my
suggestion will work just fine if his dates include a time component - after
all, he'll only get values from the next day when the time component is
exactly midnight (or as close to midnight as the limits of Access's time
values allow).

You've certainly covered all the points but I'm still not sure about
your 'just fine' conclusion -- midnight is still a time element and
the one most commonly encountered

Consider:

end_date = #2007-03-31 00:00:00#
test_date = #2007-04-01 00:00:00#

Adding one day to the last day of March -- whatever the time element
of the end date -- will pick up the test date which is within the
month of April, surely not desirable. For such reasons I prefer my
approach i.e. take the end date with no time element (or midnight if
you prefer) then add the time element #23:59:59#, being one day less
one measure of the smallest time granule (one second).

Jamie.

--

 

[Rob Parker]

You're a fine devil's advocate, Jamie ;-)

Rob

PS. We still haven't heard from the OP as to whether his problem is solved
yet, and (if it is) what worked for him.

 

[onedaywhen]

You're a fine devil's advocate, Jamie ;-)

[After consulting wikipedia] Not so (though I'd have given you
'pedant', 'too serious', 'can't take a compliment', etc <g>).

My 'inclusive and conclusive end dates' is not argument for argument's
sake; rather, it's well thought out (read: plagiarized), well aimed
and one which I deploy often, both professionally and in newsgroups
e.g.

http://groups.google.co.uk/group/microsoft.public.access.queries/msg/b3cda6e14067161e

Jamie.

--