Forum Discussion

Emanuele175's avatar
Emanuele175
Copper Contributor
Apr 27, 2023

IIF formula not working...

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

  • John-project's avatar
    John-project
    Silver Contributor
    Apr 27, 2023

    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

    • Emanuele175's avatar
      Emanuele175
      Copper Contributor
      Apr 28, 2023

      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!

      • John-project's avatar
        John-project
        Silver Contributor
        Apr 28, 2023
        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

Share

Resources