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