• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

Report subtotals not working

Dear experts -
This one has me pulling my hair out -
I have a number of complex calculations in report footers and grand total - I tried using "=sum..." but some of the fields generated #ERROR.
So I tried removing the SUM and instead used the RUNNING SUM (OVER GROUP). This removed the error - however, I did a calculation on the records and the results were WRONG (I guess running sum consecutively adds records, not sums over the group).
any idea how I can tackle the ERROR code?
The formula basically has 2 pieces - when I isolate each piece and do the sum THIS WORKS FINE (!!). When I combine them in the Sum(IIF statement, it gives the ERROR.
Here is one example of a formula in a field:
=Sum(IIf(Nz([quoteamnt],0)>0 And dateinsameperiod([effectivedate],"q",False,1)=True,[effectiveamt],DaysOverlap(DateSerial(Year(DateAdd("q",1,Date())),(Int((Month(DateAdd("q",1,Date()))-1)/3)+1)*3-2,1),DateSerial(Year(DateAdd("q",1,Date())),(Int((Month(DateAdd("q",1,Date()))-1)/3)+1)*3+1,0),[effectivedate],[effectivedate]+365,True)*[effectiveamt]/365))

  • 2
1 Solution
terpsichoreAuthor Commented:
Could this be because the source date fields are datetime fields - maybe i am combining dates/NOW()/DATE() with pure dates?
Jeffrey CoachmanMIS LiasonCommented:
Try adding a control to the Groupfooter for each segment of the expression.  Then see if all of the individual values add up to the desired total (Check this for all groups)
Then you can simply add them together NZ(txt1)+NZ(txt2), ...etc

Running sum "over group" will sum for each group individually (then resets for the next group) So I am not quite sure why this gave you the wrong answer...

The sum Function likes to look at a field in the Report's Recordsource.
I learned a long time ago that if you what the correct value in the report (this is especially true of formatting) , then make the source values correct in the query.

In some cases like this, that I have seen, one of your expressions may not have included all the criterion for the group, ...for example your expression might need a dsum), not just a sum.
Sum() will sum all the values in the field for that group, (or the entire recordsource), ...with Dsum, you can specify other criteria:
For example:
=Sum(Freight) will sum the entire Freight field, for the group of the entire Report.
=Dsum("Freight", "YourTableOrQuery", "EmpID=" & me.EmpID)
...will sum the freight only  for the current EmpID in that group.

Not sure if all your "dateinsameperiod" function in your expression will calculate all value contingencies correctly though...

Lets see what other experts might post...

terpsichoreAuthor Commented:
very insightful. In the end, I added the calculated value to the underlying query and summed that, and this worked. Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now