Calculated file in a Report

[TKinzer]

I have a field in a report that I am trying to calculate another field from. I am trying for "If [Field Name] > 500 then [Field Name] - 250 ElseIf [Field Name]< 500 then [Field Name] /2"
I have worked it several different ways and keep comming up with an error. Any suggestions?

 

[Mel Henderson]

I think this should work for you, If your report is based on a form add a text field to your form and for the control source enter the following
IIf([field 1]>500,[field 1]-250,[field 1]/2)

field 1 being the name of your field, then add this field to your report it should give you what you want. Good Luck.

 

[TKinzer]

Mel,
My report is based on a Query instead of a Form. I tried your logic in a text box on my report anyway, but it gave me "Syntax error(comma)in query expresion" message. Thank you for trying. Any more suggestions?

 

[Travis]

Try this:

Create a private function on your report.

Set the calulated fields control source as follows:

=MyCalculation([Field Name])

Example of Function:

Private Function MyCalculation(byVal lValue as Long) as Long

If lValue > 500 then
MyCalculation = lValue - 250
ElseIf lValue < 500 then
MyCaluclation= lValue /2
end if

End Function

This will do the calculation that you want on the report.