Calculate a grand total from a calculated field on a form

[stanger1]

I have a problem calculating the sum of another calculated field. Here is my calculated field, called total1.

=IIf([TYP_CHARGE1]="E",[QTY1]*[UNIT_PRICE1],[hours1]*[Unit_price])

I need to calculate the grand total for the total1 field. Any help would be greatly appreciated.

 

[Peter D]

Have you tried

=sum(total1)

or

=sum(IIf([TYP_CHARGE1]="E",[QTY1]*[UNIT_PRICE1],[hours1]*[Unit_price]))

In your form footer section?

 

[stanger1]

Thanks for the reply!! Yes, I have tried both of the suggestions that you sent with no luck.

 

[rpadams]

Make another summation query

I've run into this before, usually when I have a row total calculated that is not in the detail section. I think the problem has something to do with Access not keeping results from one section to another.

It's not elegant, but the way I get around it is to make a quick query that does the calculation before the report is run. For example, I would create a field as total1:Iif(..your formulas here) etc. and use the grouping key to compute the sum first. I then link this to the query you probably have under your report already and use the field from this combined query in the data source for the report. You can do this in a minute or two.

Since total1 is just an ordinary field as far as the report is concerned and it is already calculated, the sum function works like you think it should.

There is probably a better way to do this with a fancy sum expression in the report, but this quick and dirty system does the job. If there is a better way, I would like to hear about it.

Ralph

 

[stanger1]

Ralph,

Thanks for your reply. I think your suggestion will work.