Report Date Ranges

[JoeJ]

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

 

[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.

 

[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.