Report Year Problem

[Andrew Glennie]

Hi All,

I have created a report which details the previous month's training. The
header for this report run this month (January 2009) should say "Report for
December 2008 - Page 1" but it says "Report for December 2009 - page 1". This
is clearly somethnig that only occurs in January.

I have created code which I thought/hoped would fix this but it didn't:

="Report for " & Format$(DateAdd("m",-1,Now()),"mmmm") & " " &
IIf(Month(Now())=1,Format$(DateAdd("y",-1,Now()),"yyyy"),Format$(DateAdd("y",0,Now()),"yyyy")) & " - Page 1"

Can anyone spot the point I have missed?

Regards

Andrew

 

[Rick Brandt]

Hi All,

I have created a report which details the previous month's training. The
header for this report run this month (January 2009) should say "Report
for December 2008 - Page 1" but it says "Report for December 2009 - page
1". This is clearly somethnig that only occurs in January.

I have created code which I thought/hoped would fix this but it didn't:

="Report for " & Format$(DateAdd("m",-1,Now()),"mmmm") & " " &
IIf(Month(Now())=1,Format$(DateAdd("y",-1,Now()),"yyyy"),Format$(DateAdd ("y",0,Now()),"yyyy"))
& " - Page 1"

Can anyone spot the point I have missed?

Regards

Andrew

="Report for " & Format(DateSerial(Year(Date()), Month(Date())-1,1),
"mmmm yyyy") & " - Page 1"

 

[John Spencer (MVP)]

How about a simpler expression
= "Report for " & Format(DateAdd("m',-1,Date()),"mmmm yyyy") & " - Page 1"

Your problem was you used "y" instead of "yyyy" when using the DateAdd
function. "y" is the day number of the year - that is a number from 1 to 366
so you were subtracting 1 day from the current date instead of 1 year from the
current date

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Andrew Glennie]

Thanks both Rick and John for your solutions. Much appreciated.