date range is 20th of month to 19th of following month 1

[tglantz]

I need a report to show records going back 1 year or more, but also which can be run dynamically going forward.
The report date ranges are from the 20th of a month to the 19th of the following month.
Also need to show each "month range" on a separate page, in descending order.
Finally, the details for each day are grouped, and the total (count) is shown in the Report Footer.
Using CR 8.5 Standard
Access 2000 database table

I have looked through the forums but didn't find a specific solution.

I can get the report to work for 1 month, but can't figure out how to get it to work repeatedly. Here is my current info:
Parameters: None
FormulaChkDate (the date field in Access is a string value)

If IsDate ({reporting.reminder_examdate}) then 
CDate ({reporting.reminder_examdate})
else 
Date (0,0,0)

Grouping: on @ChkDate, descending order, for each day

Grand total: count of ({reporting.reminder_examdate})
in RF.

Record Selection Criteria:

{@CheckDate} >= Date(DateAdd("m", -3, Date(Year    (CurrentDate), Month(CurrentDate), 20))) and
{@CheckDate} <= Date(DateAdd("m", -2, Date(Year(CurrentDate), Month(CurrentDate), 19)))

here is the output:
Reminder Date All Events
4/19/2005 1,312
4/18/2005 1,311
4/17/2005 5

**omitted middle values to
save space.

3/22/2005 1,782
3/21/2005 1,723
3/20/2005 3

Grand 33,985
Total:

Note: When complete, this report will run on a MY SQL database but I'm testing at home w/ Access.

Thanks in advance,

Tim

 

[lbass]

You could do this by creating two parameters: {?date} which would be a discrete date parameter reflecting the last date in the range under consideration, and {?# of months} prior to the maximum date.

Use a record selection formula like:

{table.date} in dateadd("m",-{?# of months},{?date}-day({?date})+20) to {?date}-day({?date}) + 19

In the report create a formula {@month20-19}:

whilereadingrecords;
numbervar n;
numbervar i := {?# of months};
datevar x;

for n := 1 to i do(
if {table.date} in dateserial(year({?date}),month({?date})-n, 20) to
dateserial(year({?date}),month({?date})-n+1,19)
then x := dateserial(year({?date}),month({?date})-n, 20));
x

Insert a group on {@month20-19} and select "Customize group name"->"Use a formula to customize group name"->x+2 and enter:

totext({@month20-19},"MM/dd/yyyy")+" to "+totext(dateserial(year({@month20-19}),month({@month20-19})+1, 19),"MM/dd/yyyy")

Then you would go to the section expert and highlight the group footer for {@month20-19}->new page after->x+2 and enter:

not onlastrecord

-LB