Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2009
    Posts
    10

    Unanswered: IIF(variable, do math, end function)

    Hello experts....I have a variant type and if a number I want to do math, else I want to end the function or just skip the math and continue the function.
    Thanks in advance!!!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    Iif(myCol NOT LIKE "*[!0-9]*", doMaths)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    an alternative
    iif( isnumeric(myColumn),domathsstuff,0)
    which is the equivalent of
    if isnumeric(MyColumn) 'the value of MyColumn can be expressed as a number
    avariable=...whatever your mathstuff is
    else 'the value of MyColumn cannot be expressed as a number
    avariable=0
    endif

    'effectivlely avariable is the return value from the IIF function, you could make that whatever you want
    eg
    avariable = "n/a"
    ="NaN"
    ="not numeric"
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2009
    Posts
    10
    IIf(Mag NOT LIKE "none", ERPtoField = 7.02 * Sqr(ERP) / (Mag * 0.3048))

    ...MS Access is compile error, hightlighting LIKE, and expecting expression..
    FYI, Mag is Variant and returns none or a value I do math on.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    if isnumeric(MyColumn) 'the value of MyColumn can be expressed as a number
    Note this is critical. IsNumeric will return true for "1e3". My code will not. Use whichever is more appropriate.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Iif is a function, not a statement - you can't use it like that. Are you writing this in VBA? If so, use IF..THEN...ELSE.
    Code:
        If Not mag Like "none" Then
            ERPtoField = 7.02 * Sqr(ERP)
        End If
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    Code:
    Iif(myCol NOT LIKE "*[!0-9]*", doMaths)
    FAIL -- Iif takes 3 parameters



    Quote Originally Posted by cormier35
    IIf(Mag NOT LIKE "none", ERPtoField = 7.02 * Sqr(ERP) / (Mag * 0.3048))
    why code the NOT? just put the calc into the false parameter

    also, why use LIKE if you're not going to use a wildcard?

    IIf(Mag = "none", NULL, 7.02*Sqr(ERP)/(Mag*0.3048)) AS ERPtoField

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2009
    Posts
    10
    Thanks everyone!!! Yes, used standard If statement not IIf and worked fine

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Moreover, IIf can lead to subtle errors (vicious, I would say) because both members (True part AND False part) are always evaluated! Try this:
    Code:
        Dim X As Variant
        Dim Y As Variant
        X = InputBox("Type something:")
        Y = IIf(IsNumeric(x), MsgBox("X is numeric"), MsgBox("X is not numeric"))
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •