Sum of calculated field in report

[whisp]

Hi all,

In a report i would like to sum calculated values. However, Access 2013 doesn't support this, thus i'm looking for a workaround.

It's a journal with one row showing hours per "task". The report is grouped by day and the hours are summed up correctly in the group footer. There's one more row in the group footer with a constant value of 0.5 (hours, pause time, per day). And there's a third row in the group footer, which adds this 0.5 to the hours sum (a calculated field called tboEffectiveHours). In the report footer i want to sum this tboEffectiveHours.

What can i do to calculate this sum? I see no way to include this in the query, since the +0.5 is on a daily basis, while the rest is per task. I tried it with vba but the only semi-suitable event seems to be the paint event, but i didn't find one that fires one time only without user input.

Any hints?

Thank you,
whisp

[June7]

Only thing I can think of is to somehow get a count of the days (difference of the Max and Min dates of the report maybe?) in the report footer and multiply that by 0.5 then add to the hours sum.

[whisp]

Something similar i already tried. I summed tboEffectiveHours in a global variable and wrote that value into the final textbox. But then there's the problem with the event: I only found the paint event, and this is executed ways too often.

[rpeare]

Can you provide a sample of your database, with enough junk data to simulate the problem? Are you aggregating your data such that each day has a single record or are you aggregating your data on the report so there are multiple records for each day in the query driving your report?

If it's the former you could have, in your report footer a formula like =dcount("*", "Queryname") to give you the number of days represented in your query then perform your calculation of effective time using this calculation like
=dcount("*", "Queryname") * .5

If it's the latter you may have to do some code to cycle through the recordset to get the number of individual days represented in your dataset or count them something like:

dim db as database
dim rst as recordset

set db = currentdb
set rst = db.openrecordset("SELECT [DateField] FROM QueryName GROUP BY [DateField]")
rst.movelast
DaysInReport = rst.recordcount
rst.close
set rst = nothing
set db = nothing

in the ON LOAD or ON OPEN event of your report

[June7]

My suggestion does not involve VBA. It would use Min and Max aggregate functions in the report footer.

[whisp]

Thank you both. Your answers helped me to solve the problem. I finally used VBA with a similar approach stated by rpeare in the ON OPEN event, but used it to calculate the difference between by openargs passed dates.