Monthly Report Based on Dates

[Graham]

Can anyone tell me how to produce a report based on months. I have a small
database that you can select a date from a calender which then saves it in a
table as part of the record but can't seem to work out how to produce the
report based on a between date?

Rgds

Graham

 

[Rui]

Graham,

Edit the recordsource of the report to include the desired dates. i.e. the
query should be something like:

select [all fields you need] from [mytable] where [start date] >= #start
date# and [end date] <= #end date#;

(start and end dates could well be refering to two textboxes on a open form)

Cheers

 

[Ken Sheridan]

Include parameters in the report's underlying query so that when the report
is opened you'll be prompted for the start and end dates:

PARAMETERS
[Start date:] DATETIME,
[End date:] DATETIME;
SELECT *
FROM YourTable
WHERE YourDate >= [Start date:]
AND YourDate < DATEADD("d",1, [End date:]);

A couple of points:

1. Declaring date/time parameters is always as good idea as otherwise a
parameter entered in short date format might be interpreted as an
arithmetical expression and give the wrong result.

2. Defining the end of the date range as before the day after the end date
makes it more bullet-proof as it takes account of the possibility of the
date/time values inadvertently having a non-zero time of day (which could be
there without you seeing if the dates are not formatted to show times of day,
the usual culprit being the inappropriate use of the Now() function to enter
a date). Otherwise any rows with such date/time values during the final day
of the range would not be returned as <= a date means on or before midnight
at the start of the day, there being no such thing in Access as a date value
per se, only specific date/time values. A date entered without a time is
midnight at the start of the day, not the day as a whole. The same applies
if you use a BETWEEN….AND operation to define the date range.

In your case as you are selecting the dates from a calendar control they
should all have zero times of day values, so you'd be on safe ground, but the
above method covers all bases.

Ken Sheridan
Stafford, England

 

[Lars Brownie]

You may want to check out
http://www.fmsinc.com/TPapers/access/Reports/monthly/index.html

It shows you a nifty way to create a monthly report.

Lars