Counting Months

[xzero1484]

Hi,



I could use help on this one...

I'm trying to figure out incremental savings for each part and having trouble building a query to calculate savings for a 12 month period. Each part will have a different starting month, then will need to record savings for 12 months starting then. An Example:

Part A starts saving money in June 2010. Query should stop recording/showing savings after May 2011 (including May 2011)

Part B starts saving money in April 2010. Query should stop recording/showing savings in March 2011(including March 2011)

I'm trying to use the DateSerial funcition and can figure out the stop month, but I think there's an easier way to do this. I just want to make sure that the part savings starts recording at the start month and end after 12 months of accruing savings. Any help would work!

Thanks,

Ray

[ConneXionLost]

Hi,

EndDateToTheDay: DateSerial(Year([YourStartDate])+1,Month([YourStartDate]),Day([YourStartDate])-1)

will give you the full year ahead (less one day)

for example:

YourStartDate	EndDateToTheDay
30 Dec 2000	29 Dec 2001
31 Dec 2000	30 Dec 2001
01 Jan 2001	31 Dec 2001
02 Jan 2001	01 Jan 2002
03 Jan 2001	02 Jan 2002

EndDateToTheMonth: DateSerial(Year([YourStartDate])+1,Month([YourStartDate]),0)

will give you the end of the 12th month

for example:

YourStartDate	EndDateToTheMonth
30 Dec 2000	30 Nov 2001
31 Dec 2000	30 Nov 2001
01 Jan 2001	31 Dec 2001
02 Jan 2001	31 Dec 2001
03 Jan 2001	31 Dec 2001

Cheers,