URGENT (Please help) - Problem with Previous Month

[SmileyFace]

I am trying to retrieve data for the previous month...since it is Jan right now, the formula Month(CurrentDate)-1 generates a '0' which gives an error: "A month number must be between 1 and 12". To bypass this the following code was written, but doesn't seem to work...please note what is highlighted...

if {?ReportPeriod} = "CurrentMonth" then
if {?ReportSite} = "X" then
"Created in " + ToText(CurrentDate, "MMMM yyyy")
else
"Created in " + ToText(CurrentDate, "MMMM yyyy") + " for " + {?ReportSite}
else
if {?ReportPeriod} = "PreviousMonth" then
if Month(CurrentDate) = 1 then (...here is where it should go in the loop)
if {?ReportSite} = "X" then
"Create in December " + ToText(Year(CurrentDate)-1)
else
"Created in December " + ToText(Year(CurrentDate)-1) + " for " + {?ReportSite}
else (...but it still seems to be coming into the 'else' section and giving that error)
if {?ReportSite} = "X" then
"Created in " + ToText(DateTime(Year(CurrentDate), Month(CurrentDate)-1, Day(CurrentDate)), "MMMM yyyy")
else
"Created in " + ToText(DateTime(Year(CurrentDate), Month(CurrentDate)-1, Day(CurrentDate)), "MMMM yyyy") + " for " + {?ReportSite}
else
if {?ReportSite} = "X" then
{?ReportPeriod}
else
{?ReportPeriod} + " for " + {?ReportSite}

I would really appreciate any help offered with this. Thanks!!

 

[rajeshgooner]

Have you tried the dateadd function around the current date.

So, if you have the parameter specified as 'Previous Month' then you can use dateadd to minus one month from the date and you can then do what you want to your heart's content with that new date?

 

[SmileyFace]

Thanks for your reply Rajeshgooner! Could you tell me how I could use it inside my code to do the same thing? Also, why would the above code not work? It worked all along, except now because it is Jan and Month(CurrentDate)-1 becomes 0 on execution which isn't acceptable. Why is it going to the 'Else' part and calculating Month(CurrentDate)-1 when it should just execute the 'If' part of the statement?

 

[SmileyFace]

Hey RajeshGooner! Just wanted to let you know that by using 'dateserial' instead of 'Datetime' function, the error went away. Well, thanks for your help anyway!!