IIF formula not working...

Copper Contributor

I am struggling with something that should be simple:

 

In a custom Organization Field I need to put this formula:

 

iif(Csng([Lavoro])>0,(CSng([Costo]) / CSng([Lavoro]) * CSng([Lavoro Definitivo])),0)

 

[Lavoro] is like the standard field "Work" in English version

[Costo] is like the standard field "Cost" in English version

[Lavoro Definitivo] is a custom Organization field I created the tyoe of which is Cost.

The type of [Lavoro Definitivo] is Duration

 

It keeps telling me that the formula contains mistakes, but I really don't know what.

 

Without the IIf, the formula: (CSng([Costo]) / CSng([Lavoro]) * CSng([Lavoro Definitivo])) works fine.

The goal of it is to avoid the "#ERROR" in case [Lavoro]=0

 

Thank you

3 Replies

@Emanuele175 

Thanks for clarifying your language terms with English terms. However, one part doesn't make sense. Namely, these statements:

[Lavoro Definitivo] is a custom Organization field I created the tyoe of which is Cost.

The type of [Lavoro Definitivo] is Duration

 

It says Lavoro Definitivo is both type Cost and type Duration. So which is it?

 

I put this formula in a test file and it results in no errors:

Cost1= IIf(csng([Work])>0,(csng([Cost])/csng([Work])*csng([Cost2])),0)

 

Also, which field did you customize with the formula (e.g. TextX, CostX, DurationX, etc.)?

John

@John-project

 

Yes, John, my mistake.

 

[Lavoro Definitivo] is of type duration, being a "Work"

[Costo Definitivo] is of type Cost 

 

Final Formula should be: 

IIf (csng([Lavoro])>0, (csng([Costo]) / csng([Lavoro]) * csng([Lavoro Definitivo])), 0)

 

it didn't work, anyways, until I wrote the condition with the right blank characters in the right places...

IIf (csng([Lavoro]) > 0,......

it seems weird to me, but syntax has its rules, as far as I can see.

 

Thank you!

Emanuele175,
Are you saying it now works after you inserted spaces in your formula? If so, I've NEVER seen that before. In fact, when I inadvertently include extra spaces in a formula, they are automatically deleted.
John