Converting sql case expression

Question

I'm trying to take the following sql case statement and convert it to Access 2010 as a calculation for a column. I've looked at the IIF statements but have received errors trying to sort it out. Thank you for any help.

case 
   when left(Tiers,4) = 'Tier' and isnumeric(right((left(Tiers,7)),2)) = 1 then right((left(Tiers,7)),2)
   when left(Tiers,4) = 'Tier' and isnumeric(right((left(Tiers,7)),2)) = 0 then right((left(Tiers,6)),1)
   else Tiers
end

;

yeah... you are going to need to show us what you tried and what errors you got otherwise this isn't a question it is a request for a consultant. — Hogan, Jun 10 '16 at 19:07
Possible duplicate of [Case expressions in Access](http://stackoverflow.com/questions/772461/case-expressions-in-access) — sstan, Jun 10 '16 at 19:18

eshirvana · Answer 1 · 2016-06-10T19:12:51.250

0

you can only use Case statements in access in VBA Code. and iif should work as well in access like this iif(left(Tiers,4) ='Tire',iif(isnumeric(right((left(Tiers,7)),2)) = 1,right((left(Tiers,7)),2),right((left(Tiers,6)),1)), Tires)

edited Jun 10 '16 at 19:12

answered Jun 10 '16 at 19:05

eshirvana

23,227
3
22
38

score 0 · Answer 2 · edited May 23 '17 at 10:34

You can use IIF() as you mentioned and it will likely be a pain. There is also SWITCH function available which will likely make it easier for you to convert your case statement instead of nested IIF()s

Switch( expr-1, value-1 [, expr-2, value-2 ] … [, expr-n, value-n ] )

https://support.office.com/en-us/article/Switch-Function-d750c10d-0c8e-444c-9e63-f47504f9e379

This is a duplicate question of: Case expressions in Access.

Converting sql case expression

2 Answers2