Changing the label heading on a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am creating a report for rep's called last months sales. I have written a
query that will calculate on a rolling basis what the sales were for last
month. So if I ran it today it would give me April 2005. If I ran it on
June 1st, it would give me May. I also wrote a query to give me what the
monthly sales were a year ago. So if I ran that query today it would give me
April 2004.

What I want to do is the create a report with these figures in it. However,
I don't just want to put LastMonthSales on the report column headings. I
want to put April 2004 and April 2004. Can someone tell me how to do this or
what my options are?

Thanks,
 
let's call the text box on the report where you want the date to appear
txtSalesPeriod. Also, let's assume we get the month and year from the form
that calls the report.
In the Open event of the report:
Me.txtSalesPeriod = [forms]![frmMyReport]![txtMonth] & " " &
[forms]![frmMyReport]![txtYear]
 
Sorry, Chuck, I meant to add that if you are running for last month, then you
just have to subtract a month
=dateadd("m",-1,date)
 
Hi,

Thanks for your help. I am actually not basing the date on a form. I wrote
a query that gets both last months sales (April 2005) and last months sales
for last year (April 2004). So it looks like I want to add a text box for
last month and last year, last month.

For Last Month I typed in = format(Date(),"mmmm") & " " &
format(Date(),"yyyy")
which gave me May 2005. So if I want to back it up a month how would I
subtract one month from this? Also, How would I handle a year ago last month
(ie April 2004)?

Thanks again,
 
Use the Dateadd() function. Easiest way to find info is in Access or VB Help.
To subract a month:
=Dateadd("m",-1, MyDate)
A year:
=Dateadd("y",-1, Mydate)
Check Help for the syntax, I did this on the fly
 
Back
Top