IIF ( ) statement in a report with Caluated fields

[ehonwollen]

I'm trying to but together a function , formula or something ( IN A REPORT) that would check one field for value >0 then use that value .if the value is <0 use the value in another field. Listed below are the actual fields I'm using and the formula used to populate those fields. I need to create a Oblgated field based on this crteria.. Is this possible

Thanks in advance?? oh If you can't tell I'm new at this and any help s highly apprectiated

Actual.Spent is calculated field that includes this formula

=[TotActAirfare]+[TotActCarRental]+[TotActLodging]+[TotActMealsAndIncd]+[TotActMisc]

EST.TRVL is calculated field that includes this formula
=[EstAirfare]+[EstCarRental]+[EstLodging]+[EstMealsAndIncd]+[EstMisc]



Obligated.Fund this field need to check Actual.Spent for a (calculated) value >0 if the value is greater than 0 use that value. If Value is < 0 ;Use the value (calculated) EST.TRVL

This is my attempt to make this happen:
IIF ([Actual Spent] = 0,
(EstAirfare+EstCarRental+EstLodging+EstMealsAndIncd+EstMisc),
IIF [Actual Spent]>0,(TotActAirfare+TotActCarRental+TotActLodging+TotActMealsAndIncd+TotActMisc)),

 

[CosmoKramer]

Try:

IIF ([Actual Spent] < 0,
 (EstAirfare+EstCarRental+EstLodging+EstMealsAndIncd+EstMisc), 
(TotActAirfare+TotActCarRental+TotActLodging+TotActMealsAndIncd+TotActMisc))

You would probably also have to account for if [Actual Spent] = 0 (either <= or >=)......

 

[ehonwollen]

CosmoKramer

Thank You your response. It worked great