Syntax Error when using IIf Function

Question

Hi All,

I am trying to program an expression in a query to caculate the percentage of a budget line used. Basically just dividing the realized costs by the budgeted amounts. 

For some of the budget lines on a handfull of records, there were no funds budgetted or used. Meaning there are zeros for both amounts. This is yielding a #Num! error that I would like to hide, when it happens. To do so I have read that one can use the IIf function to solve this issue. However, when I do so, I keep getting syntax errors. 

Currently my expressions look likes this (for an example):

Perc-Housing: IIf([FIN_Housing]=0,0,([REAL_Housing]/[FIN_Housing])

However when I use this I get an error that says I have a syntax error or that I am missing an operator. 

Would anyone be able to point me in the write direction as how to fix this?

Answer 1

Do you use comma as decimal separator? If so, the expression should be

Perc-Housing: IIf([FIN_Housing]=0;0;[REAL_Housing]/[FIN_Housing])

Answer 2

Hi Hans, 

Thanks for the reply. 

Unfortunately, I am am now seeing an error on saying that the 0,0 is an invalid number. Adding a space does not appear to fix this. 

Any advice you can provide would be of great help.

Answer 3

You have an extra opening parenthesis.

Perc-Housing: IIf([FIN_Housing]=0,0,[REAL_Housing]/[FIN_Housing])