asked on

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))

Thanks!!

terpsichore

ASKER

Could this be because the source date fields are datetime fields - maybe i am combining dates/NOW()/DATE() with pure dates?

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

terpsichore

ASKER

very insightful. In the end, I added the calculated value to the underlying query and summed that, and this worked. Thanks.