Calculate records with dates from past week and month

[Teddy]

I have a table in which each record has an action date. I’d like to create a
report with two fields. One field will calculate the number of records with
dates from the past week. The second field will calculate the number of
records with dates from the past month. Can you tell me how to do that?

 

[Jeff Boyce]

Teddy

Remember that reports have "controls" (tables have "fields") that can
display data. That data can come from tables or queries, or could come from
an expression.

You could use an expression in a control that calculated the number of
records within "the past week" (more definition, please), and another for
within "the past month". One approach might be to look at the DCount()
function.

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Dale Fye]

Something like this should work:

SELECT SUM(IIF([DateField] BETWEEN DateAdd("d", -7, Date()) AND Date(), 1,
0)) as PastWeek,
SUM(IIF([DateField] BETWEEN DateAdd("m", -1, Date()) AND Date(), 1, 0)) as
PastMonth
FROM yourtable

This uses the current date as the base for PastWeek and PastMonth. If you
actually want LastMonth and LastWeek, you will probably need to use the
DateSerial function.

 

[Teddy]

This is perfect. Thank you Dale!

Something like this should work:

SELECT SUM(IIF([DateField] BETWEEN DateAdd("d", -7, Date()) AND Date(), 1,
0)) as PastWeek,
SUM(IIF([DateField] BETWEEN DateAdd("m", -1, Date()) AND Date(), 1, 0)) as
PastMonth
FROM yourtable

This uses the current date as the base for PastWeek and PastMonth. If you
actually want LastMonth and LastWeek, you will probably need to use the
DateSerial function.
----
HTH
Dale

I have a table in which each record has an action date. I’d like to create a
report with two fields. One field will calculate the number of records with
dates from the past week. The second field will calculate the number of
records with dates from the past month. Can you tell me how to do that?

 

[Teddy]

Jeff that is helpful. Thank you for clarifying that for me. I appreciate
your feedback.