Average data for previous month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi... i need a formula that will average the data for a previous month. i
tried using the below statement but it doesn't seem to work, please help.


Between Date( ) And DateAdd("M", -1, Date( ))
 
Isn't month represented by m and not M?
--
***************************
If the message was helpful to you, click Yes next to Was this post helpful
to you?
If the post answers your question, click Yes next to Did this post answer
the question?
 
thank you ever so much it works like a charm.

Ofer Cohen said:
Try

Where Format([DateFieldName],"mmyyyy") = Format(DateAdd("m", -1,
Date()),"mmyyyy")
--
Good Luck
BS"D


Nero said:
hi... i need a formula that will average the data for a previous month. i
tried using the below statement but it doesn't seem to work, please help.


Between Date( ) And DateAdd("M", -1, Date( ))
Click to expand...
Click to expand...
 
"Nero" <wrote:
hi... i need a formula that will average the data for a previous month. i
tried using the below statement but it doesn't seem to work, please help.


Between Date( ) And DateAdd("M", -1, Date( ))
Click to expand...

if you want the full range of previous month, I'd use


WHERE
[DateField] >= DateSerial(Year(Date()), Month(Date()) -1, 1)
AND
[DateField] < DateSerial(Year(Date()), Month(Date()), 1)
 
Nero said:
thank you ever so much it works like a charm.
Click to expand...

The solution posted by Gary Walters is the better way to go because it is a
more efficient query design. If you have an index on your date field Gary's
criteria will be able to utilize it whereas the other posted solution will
have to do a fiull table scan.

Not a big deal if the table is small and local, but could make a big
difference in larger networked tables. The rule of thumb is to never set
criteria against an expression if there is an alternative that lets you set
criteria directly against the field. As soon as you test against an
expression the query engine can no longer use any indexes. The problem is
that the less efficient methods are often easier to conceptualize and take
fewer keystrokes to implement so you see them used a lot.

Having an expression in the actual criteria (the right-hand side of the
expression) is fine as long as it does not contain references to other
fields.
 
To get the previous month try the following critera.

Between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()), Month(Date()),1-1)

If your date field contains a time, then you will need to modify the above.
 
Back
Top