If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculate records with dates from past week and month
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? |
#2
|
|||
|
|||
Calculate records with dates from past week and month
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. "Teddy" wrote in message ... 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? |
#3
|
|||
|
|||
Calculate records with dates from past week and month
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" wrote: 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? |
#4
|
|||
|
|||
Calculate records with dates from past week and month
This is perfect. Thank you Dale!
"Dale Fye" wrote: 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" wrote: 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? |
#5
|
|||
|
|||
Calculate records with dates from past week and month
Jeff that is helpful. Thank you for clarifying that for me. I appreciate
your feedback. "Jeff Boyce" wrote: 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. "Teddy" wrote in message ... 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? . |
Thread Tools | |
Display Modes | |
|
|