Calculation on a Report

[Guest]

I'm trying to create a formula in the report footer that will calculate
values greater than 12. For example, if a value is 12 or less I don't want to
calculate it, if the value is greater than 12 I want to multiply the amount
greater than 12 by 5.5 (if 16 then 5.5 X 4). Your help is appreciated.

 

[Marshall Barton]

I'm trying to create a formula in the report footer that will calculate
values greater than 12. For example, if a value is 12 or less I don't want to
calculate it, if the value is greater than 12 I want to multiply the amount
greater than 12 by 5.5 (if 16 then 5.5 X 4). Your help is appreciated.

Try an expression like:

=IIf(thevalue > 12, 5.5 * (thevalue - 12), thevalue)

 

[Guest]

Marshall,

I tried that formula. I have one record with a value of 16. I should get 22
as the sum (16-12=4*5.5). With the IIF statement Im getting 12?

 

[Marshall Barton]

I don't see how that's possible using the expression I
posted. Could you Copy/Paste the text box's ControlSource
property into a reply so I can see **exactly** what you
have.

 

[Guest]

Marshall,

I ended up changing the sql statement on the report and summed the text box.
Thanks for your help.

I don't see how that's possible using the expression I
posted. Could you Copy/Paste the text box's ControlSource
property into a reply so I can see **exactly** what you
have.
--
Marsh
MVP [MS Access]

I tried that formula. I have one record with a value of 16. I should get 22
as the sum (16-12=4*5.5). With the IIF statement Im getting 12?

 

[Marshall Barton]

Sum?? Where did a Sum come into the question? If that's
was what you were tring to do all along, you should have
said so.

Maybe the latest issue is that you were trying to Sum a text
box. This will never work because the aggregate functions
only know how to operate on fields in the RecordSource
table/query. Moving the expression to the query is a godd
way to resolve the issue. The other is to use a text box
that Sums the expression, not a text box with the
expression.