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

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Mary
    #1

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

    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
    Tags: None
    • Mal Reeve
      #2
      Re: Need the Date Expression to Determine Past 12 months of Data When Month and Year Entered

      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" <MaryMAnnie@aol .com> wrote in message
      news:db956f7c.0 404301727.17669 34c@posting.goo gle.com...[color=blue]
      > 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[/color]


        Comment

        • Mal Reeve
          #3
          Re: Need the Date Expression to Determine Past 12 months of Data When Month and Year Entered

          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....
          FirstOf11Months Back: DateSerial(Year (DateAdd("m",-1,[Enter Start
          Date])),Month(DateAd d("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" <MaryMAnnie@aol .com> wrote in message
          news:db956f7c.0 404301727.17669 34c@posting.goo gle.com...[color=blue]
          > 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[/color]


            Comment

            Previous template Next
            Working...