Calculate records with dates from past week and month

  • Thread starter Thread starter Teddy
  • Start date Start date
T

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?
 
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.
 
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.
 
This is perfect. Thank you Dale!

Dale Fye said:
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



Teddy said:
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?
Click to expand...
Click to expand...
 
Back
Top