Calculated Fields with iif statements

Copper Contributor
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 is have the field set to 0 if the calculation is less than 0 to prevent any skewed reporting. I suspect I would use the IIf function to do this, but I'm not sure how to go about it. My attempts so far have returned improper syntax errors.

My expression for one of the fields is:
Day([Close Date])-Day([Created Date])

I tried it as:
IIF(Day([Close Date])-Day([Created Date])<"0",[Days Active]="0")

Am I able to add it on the expression or would it require a query? How, exactly, should I apply it to the fields so that it changes the result to 0 in the table?

Thank you,
Heather
2 Replies

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.

 

@hrandall 

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])