Report on weekly basis

[Guest]

Hi All,

Beginner seeking for helps.

Having table:
Date Sale
11/1/2004 $10
11/2/2004 $2
11/23/2004 $7
12/3/2004 $5
12/7/2004 $6
12/13/2004 $4

I click on NOVEMBER in my Form. Report shud be in:
Sales for November: $19
Week 1 Week 2 Week 3 Week 4 Week 5
$12 0 0 $7 0

If I choose DECEMBER on my Form. Report shud be in:
Sales for December: $15
Week 1 Week 2 Week 3 Week 4 Week 5
$5 $6 $4 0 0

Is that possible???
Thanks all,
Grace

 

[Larry Linson]

You could include a Calculated Field in a Query that you use as the Control
Source for the Report, which calculates the month and include a criteria
that the user enters. "Date" is not a good name to use for a Field, because
it is an Access reserved word -- the Date() builtin function returns today's
date. So I am going to use TranDate to represent the date of the
transaction.

In the QueryBuilder grid, use

ThisMonth: Format([TranDate],"Mmm")

and in the criteria line below it put

[Enter the month's abbreviation]

And it will display an input box asking for the month when you run the
report.

It could be done a little smoother, perhaps, with a form allowing the user
to select the month, and VBA code to construct the WhereCondition argument
of a DoCmd.OpenReport that is used to open the report. But the above
requires no code...

Larry Linson
Microsoft Access MVP