Average data for previous month

[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( ))

 

[Guest]

Try

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

 

[Guest]

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?

 

[Guest]

thank you ever so much it works like a charm.

Try

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

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( ))

 

[Gary Walter]

"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( ))

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)

 

[Rick Brandt]

thank you ever so much it works like a charm.

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.

 

[John Spencer]

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.