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 !!!
7601105166084Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
You can simplify that expression by using the Switch function, instead of all the embedded IIF's. Check Help for the syntax.

mx
0
 
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], "")
0
 
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"

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Jim HornConnect With a Mentor Microsoft 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"))))))
0
 
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.

mx
0
 
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?

Thanks
0
 
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], "")
0
 
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!

0
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.