Having difficulties with a Calculated field using IIF

REF is a Text field in Table1
In Query1 - Calculated field --- > Test_Field: IIf([REF]="K","other",IIf(CInt([REF])=1,"One",IIf(CInt([REF])=7,"Seven",IIf(CInt([REF])=12,"Twelve",IIf(CInt([REF])=5,"Half","other")))))

It was just by chance that i saw K, as if It encounters another letter - which should then return False and therefore "other"  but actually returns #error !!!
Who is Participating?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You can simplify that expression by using the Switch function, instead of all the embedded IIF's. Check Help for the syntax.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>if It encounters another letter - which should then return False
You don't have that logic anywhere.  That would be IsNumeric(Nz([Ref], "")
7601105166084Author Commented:
jimhorn I dont understand that logic :  you are saying if [REF] is null then "blank" else is [ref] numeric ?

I would rather it be :
Test_Field: IIf(CInt([REF])=1,"One",IIf(CInt([REF])=7,"Seven",IIf(CInt([REF])=12,"Twelve",IIf([REF]="K "other",)))))

So IIF .... and then.... IIF[REF] is any other value or letter then it must show "other"

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
NULL is the absense of a value, and you have to trap that in any expression.  This is called 'null handling'.
In your expression, you also want to trap any letter value other than "K"

IsNumeric(Nz([Ref], "") will trap both NULL values, and any non-numbers.

Give this a whirl (air code, so you may have to modify)...
Test_Field: IIf(Nz([REF], "")="K","other",iif(IsNumeric(Nz([Ref], "")), False,  (IIf(CInt([REF])=1,"One",IIf(CInt([REF])=7,"Seven",IIf(CInt([REF])=12,"Twelve",IIf(CInt([REF])=5,"Half","other"))))))
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
'air code' jHo, lol.  Is that the same as 'vaporware' ?

Seriously, in addition to what jHo is showing you, check out the Switch function. It will make the expression waaaay more readable and easier to check the logic.

7601105166084Author Commented:
a)syntax... ? Ok - how can I combine what jHo is saying with this :  Expr1:Switch([REF]=1,"One", [REF] =2,"two")  ?
and does Switch have an If..Then.. ?ElseIF? - If i can accomplish this with a Case statement in a function - I would rather do that - thats easier to read. but please see (b)

b)  Because the Field is a TEXT field and it has numbers and letters - is this the right or the only way??  by using Cint([REF]=1.. unfortunately I came across #errors and the only way around, which worked was to use Cint.

c)I have a similarish.. problem at the moment with excel - if i copy and paste to excel - will excel know the difference?

7601105166084Author Commented:
The following is now working:

Refs: IIf(Val([REF])=1,"One",IIf(Val([REF])=7,"seven",IIf(Val([REF])=12,"dd",IIf(Val([REF])=5,"wedf",IIf(IsNumeric(Nz([REF])),"","other")))))

Except the NULL part (shows #Error  where there is a null value) Thanks jHo for enlightening me there - except that that part is now not working )-:

IsNumeric(Nz([Ref], "")
7601105166084Author Commented:
I got this working by using the following:

Refs: IIf(IsNull([REF]),"NULL",IIf(Val([REF])=1,"One",IIf(Val([REF])=7,"Seven",IIf(Val([REF])=2,"Two","other"))))

I'm goin to share points between you 2,  as now I understand the concept of the SWITCH function ..

Expr1: Switch(IsNull([REF]),"NULLl",IsNumeric([ATT_SRC_CD]),"NUMERIC")

and also always to check for NULL  values,

thanks guys!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.