Forum Discussion
Calculated Fields with iif statements
Hi all, I have a split DB with 4 calculated fields that show the number of days between the dates entered in other fields. Sometimes the results are negative as one would expect. What I'd like to do i...
Hello hrandall,
I think you need to use DateDiff() for this. This is the formula I used to calculate the difference between 2 dates, returning a "0" if one date was less than another:
=IIf(DateDiff("d",[StartDate],[EndDate])<=0,"0",DateDiff("d",[StartDate],[EndDate]))
Use this formula in the Control Source of your Form or Report to calculate the difference between the dates, returning a "0" if the End Date is less than or equal to the Start Date.
Hope this helps.
- Good Morning,
I am having a similar problem. I am trying to use a calculated field to determine an amount expended from a budget. However, one of my accounts has over spent and therefore a negative number. I am getting this error for that account: #Div/0!
I am using the following formula that is working for all my accounts except the one over spent. How can I update this formula to work with negative numbers:
IIf([BALANCE]=0,0,([BUDGET]-[BALANCE])/[BUDGET])
