Need the Date Expression to Determine Past 12 months of Data When Month and Year Entered

[Mary]

I am trying to develop a query which will determine the average costs
using a rolling average of the past 12 months of data. In other
words, if I entered the Ship Month of January and the Ship Year of
2004, I would want that month plus the preceding 11 months of data to
determine an average cost.

How can that criteria be entered in a query so that each month I can
get the most current 12 months of data to determine the average costs?

Thanks for your help!
Mary

[Mal Reeve]

Enter a criteria of (fora date field...
Between dateadd("m",-11,[Enter Start Date]) and [Enter Start Date]

should work to give you a good range....
you can mess around with the Format and Cdate (or is it DateSerial??) to get
more specific...
for instance if a user enters 12/31/04...the original above would evaluate
to
between 1/31/04 and 12/31/04 ie. not give all of Jan,,
however..
using
Between #Month(dateadd("m",-11,[Enter Start
Date])/1/Year(dateadd("m",-11,[Enter Start Date]) ) and [Enter Start Date]
should give the whole of the month 11 months ago...

HTH
Mal.
"Mary" <Ma********@aol.com> wrote in message
news:db**************************@posting.google.c om...

I am trying to develop a query which will determine the average costs
using a rolling average of the past 12 months of data. In other
words, if I entered the Ship Month of January and the Ship Year of
2004, I would want that month plus the preceding 11 months of data to
determine an average cost.

How can that criteria be entered in a query so that each month I can
get the most current 12 months of data to determine the average costs?

Thanks for your help!
Mary

[Mal Reeve]

oops,,,

sorry, I should have tested rather than air-coded...

This will return the 1 day of the month - 11 months before the date
given....
FirstOf11MonthsBack: DateSerial(Year(DateAdd("m",-1,[Enter Start
Date])),Month(DateAdd("m",-1,[Enter Start Date])),1)

you could use it to make sure that the entire month of the given date is
used as well...
or consider running this query from a pop-up form that displays a drop down
calendar to select the starting month and year....

Mal.

"Mary" <Ma********@aol.com> wrote in message
news:db**************************@posting.google.c om...

I am trying to develop a query which will determine the average costs
using a rolling average of the past 12 months of data. In other
words, if I entered the Ship Month of January and the Ship Year of
2004, I would want that month plus the preceding 11 months of data to
determine an average cost.

How can that criteria be entered in a query so that each month I can
get the most current 12 months of data to determine the average costs?

Thanks for your help!
Mary