In report design I am trying to sum (=Sum) 23 fields from a query. If I sum 7 fields, I get a correct answer. If I sum 8 or more, it returns with #Error. The control source allows the lengthy function so am wondering why the error.
-
-
Hi Monica,
First off, welcome to Bytes!
Just a couple of things to help us out in trying to address your issue:- Would you please post the entire expression so we can see if that's where the problem lies? Remember to use the code tags when posting the expression.
- Is the field you are trying to sum from your query a number field or a text field?
- If you try to manually sum the numbers yourself, is there anything different about the 8th value that may be causing the expression to throw an error (i.e. is the value incredibly large or is it a negative number, etc.)?
-
Here is the enitre expression:
=Sum([SumOfAdam Robertson Block_10/11 Prelim]+[SumOfBlewett Block_10/11 Prelim]+[SumOfBrent Kennedy Block_10/11 Prelim]+[SumOfCanyon/Lister Block_10/11 Prelim]+[SumOfContinuing Ed Block_10/11 Prelim]+[SumOfCrawford Bay Block_10/11 Prelim]+[SumOfDESK Block_10/11 Prelim]+[SumOfErickson Block_10/11 Prelim]+[SumOfHomelinks Block_10/11 Prelim]+[SumOfHume Block_10/11 Prelim]+[SumOfJewett Block_10/11 Prelim]+[SumOfJVH Block_10/11 Prelim]+[SumOfKLLC Block_10/11 Prelim]+[SumOfLVR Block_10/11 Prelim]+[SumOfMt Sentinel Block_10/11 Prelim]+[SumOfPCSS Block_10/11 Prelim]+[SumOfRedfish Block_10/11 Prelim]+[SumOfRosemont Block_10/11 Prelim]+[SumOfSalmo Elementary Block_10/11 Prelim]+[SumOfSalmo Secondary Block_10/11 Prelim]+[SumOfSouth Nelson Block_10/11 Prelim]+[SumOfTrafalgar Block_10/11 Prelim]+[SumOfWE Graham Block_10/11 Prelim]+[SumOfWildflower Block_10/11 Prelim]+[SumOfWinlaw Block_10/11 Prelim]+[SumOfYahk Block_10/11 Prelim])
They are all number fields and there is nothing unusual about any of the numbers. It doesn't matter which 8 I pick (first 8 or last 8) the error comes back either way.
Thanks!Comment
-
//
I believe this error might be caused by Null field.
I see that you are trying to get the total of total fields. Why don't you get the total of the fields themselves so it becomes:
.Code:=sum([Adam Robertson Block_10/11 Prelim])+sum([Blewett Block_10/11 Prelim])...etc
This should work
Hope this helps,
Regards,
AliComment
-
You might try just dropping the =Sum() on the outside of the expression too. The sum function may have an upper limit on the expression it can contain, so just adding the fields themselves, if they are all number fields, should work just the same.
If you're having to sum the values of the fields in the expression to create total fields, as Ali suggested, then her solution should work.Comment
-
Thank you - thank you - thank you!!
This worked perfectly!Comment
-
//
Glad it worked for you. Would you kindly mark this post as answered
so it becomes more helpful for others.
Regards,
AliComment
Comment