Working with Dates

[Guest]

I need to generate a report which grabs data from the 3 previous months (ie.
Today is July 18, so I'd want to grab data from April to June).

Currently I am using the dateserial function and subracting 3 months for my
start date and DateSerial(year(date()),1,1) as my end date. But I also had
to include an if clause for Dates where the month is before April where I
have to set the year back 1 also (ie. Start date =
DateSerial(year(date))-1,month(date())+9,1).

Is there a better way to set the start date?

TIA

 

[John Vinson]

I need to generate a report which grabs data from the 3 previous months (ie.
Today is July 18, so I'd want to grab data from April to June).

Currently I am using the dateserial function and subracting 3 months for my
start date and DateSerial(year(date()),1,1) as my end date. But I also had
to include an if clause for Dates where the month is before April where I
have to set the year back 1 also (ie. Start date =
DateSerial(year(date))-1,month(date())+9,1).

Is there a better way to set the start date?

Sure: set both the start and end date in an expression which works any
time, and doesn't need any IIF:

= DateSerial(Year(Date()), Month(Date()) - 4, 1) AND < DateSerial(Year(Date()), Month(Date()), 1)

will get all of April, May, and June 2006 if run today - and all of
January 2007, December 2006, and November 2006 if run anytime next
February. DateSerial happily accepts negative numbers, months
arguments greater than 12, and day arguments greater than 31 and makes
the appropriate calculation; e.g.

?DateSerial(2006, 13, -365)
12/31/2005

?dateserial(1900, 1200, 300)
9/25/2000

John W. Vinson[MVP]

 

[Guest]

Thanks, John. Funny thing is I did do it this way the first time but didn't
realize negative numbers would work.