# Thread: IIF(variable, do math, end function)

1. Registered User
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.

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Code:
Iif(myCol NOT LIKE "*[!0-9]*", doMaths)

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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
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"

4. Registered User
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. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
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.

6. King of Understatement
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

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

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

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

9. Moderator
Join Date
Mar 2009
Posts
5,442
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
•