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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
terpsichoreAuthor Commented:
very insightful. In the end, I added the calculated value to the underlying query and summed that, and this worked. Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.