Calculation

[DAWNY2007]

Hi. I have written the following IIF statement in an Access query. The issue with this is that a couple of my lines have values where a negative value in one of the fields is being divided by a field with no value.

EAC %: IIf([EAC $]=0,0,[EAC $]/[EAC Revenue])

So in a couple of my lines, the values are:

($730.59) / 0
($134.80) / 0



How can I modify this statement to return a 0 in these instances ?

[Bob Fitz]

Try:

EAC %: IIf([EAC $]=0 OR [EAC Revenue] = 0,0,[EAC $]/[EAC Revenue])

[DAWNY2007]

Genius ! Thank you !

[Bob Fitz]

Genius ! Thank you !

Thank you for your kind words......if only it were true

[DAWNY2007]

No, that was a very fast response. I am so grateful for this forum and people like you. Have a wonderful day ! TGIF !

[DAWNY2007]

Ok, so here is another :-)

Got the $ working, but with the 0 values, some of my percentages are erroring.

So, here's my calculation for the $ EAC $: IIf([total revenue (as-billed)]=0 Or [cost total]=0,0,+1*[total revenue (as-billed)]+[cost total])
So here is my % calculation...and in cases where I have instances where I have 0 dollars, I am getting #Num! values: EAC %: +1*[EAC $]/[Total revenue (as-billed)]

[Bob Fitz]

Why do you have +1* in your formula

[DAWNY2007]

The original file is in Excel and I am trying to rewrite into Access. In Excel, they are using -1, but when I do in Access, I get different values. They are using in Excel to reverse negative dollar amounts.

[Bob Fitz]

Do you really have a field called [Total revenue (as-billed)]

[DAWNY2007]

Yes, it is a valid field. I used same field names from the Excel file.

[Bob Fitz]

Yes, it is a valid field. I used same field names from the Excel file.

It may be valid but IMHO it's a poor choice. You should avoid the use of spaces and special characters.
Can you post a copy of your db, even if it's just a couple of records and the query to illustrate the problem.

[Bob Fitz]

Yes, it is a valid field. I used same field names from the Excel file.

It may be valid but IMHO it's a poor choice.   You should avoid the use of spaces and special characters.
Can you post a copy of your db, even if it's just a couple of records and the query to illustrate the problem.

[DAWNY2007]

Sure...see attached with some dummy data. Thanks !

[Bob Fitz]

Try:
EAC %: IIf([EAC $]=0 Or [Total revenue (as-billed)]=0,0,[EAC $]/[Total revenue (as-billed)])

[DAWNY2007]

Yes ! I will do the same for the As-Sold. You are my star today ! :-)