Joined: 19 May 2011
Online Status: Offline
Posts: 106
Topic: Summing up a formula Posted: 03 Aug 2011 at 6:15am
I have a report that sums up the amunt of pieces in the field in the detail section. I have a formula in the format editor that if it exceeds 100 I want it to just show 100 and it all works fine. The problem I have is in the group footer I want to sum this field. If I run a sum on the column it gives me the number of all of it, so if one of the numbers is 1400 I have it set to display 100 and that is what I need it to add.
Joined: 19 May 2011
Online Status: Offline
Posts: 106
Posted: 03 Aug 2011 at 9:32am
I am not using either to get the number. It comes from adding smaller pieces in the databse and then in the format editor under the display string I have the formula:
if currentfieldvalue>100 then '100' else totext(currentfieldvalue,0,'')
which using my earlier example makes the column with 1400 display just 100. but when I sum the field it sums it as 1400 plus whatever else is in the column.
Joined: 22 May 2009
Online Status: Offline
Posts: 347
Posted: 03 Aug 2011 at 10:20am
I believe it is because it is taking what the actual formula/field is instead of what it is displaying. Try creating a formula that actually changes the value instead of just using the field and changing how it displays.
I have the field I am summing and then evaluting using the above formula and then reseting on change of group. I tried adding a formula in with this one to cap it at 100 but I couldn't make one work.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
