sum one month in report

[Joanna_gr]

Hi all! I hope someone can help me on this also. I have a report with fields NAME, DATE, HOURS. Date starts from 1/7/2004 and I have a text box to summarize all hours from that starting date. How can I add a new text box and get the total only for the past month?

 

[Andrew Fergus]

Hi Joanna

I'm not sure I follow this 100% (i.e. the past month?) but in your report footer create a new text box and if you want the total hours for the current month then use the following control source :

=Sum( IIf( [DATE] > DateAdd("d", -Day(Date()), Date()) And [DATE] <= Date(), [HOURS], 0))

If however you want the total hours for the last complete month, then use this as the control source:

=Sum( IIf( [DATE] > DateAdd("d", -Day(Date()), DateAdd("m", -1, Date())) And [DATE] <= DateAdd("d", -Day(Date()), Date()), [HOURS], 0))

HTH, Andrew

 

[Joanna_gr]

God bless you Andrew you are always here to help people ray: Thank you very much. The second solution is what I need. My mistake I wrote past instead of last or previous! Thanks again

 

[Andrew Fergus]

I unintentionally made an assumption in my 2nd formula which will give a funny result every now and then (on the last day of a 31 day month when the prior month has 30 days, e.g. 31 May), so use this formula instead :

=Sum(IIf([date] >=DateAdd("m",-1, DateAdd("d",-Day(Date())+1,Date())) And [date] <=DateAdd("d",-Day(Date()),Date()), [hours], 0))

Andrew

 

[Joanna_gr]

Well, to be honest I haven't noticed the mistake. Thanks for this