Object not found when adding SWITCH statement to query

Question

I have inherited another archaic and inefficient system, and this one uses 5 digit item numbers and sometimes a suffix of 1, 2, or 3 letters as an unique identifier.

The following are all examples of valid item numbers in this field. There are over 20 different suffixes used in total.

Item_Num

00001ABC
002ABC
3214
87522ATT
22P
04828P
00421

This makes the field difficult to work with, but it's something that cannot be changed. It's an enterprise wide system and too many entities use the field. For a query I am writing, I need to separate the two parts of Item_Num into what I am calling Raw_Num and Suffix. This is made difficult by the inconsistent formatting though; VAL() will not grab the "floating" 0s and LEFT() has to grab between 2 and 5 characters. Because of those restrictions, I developed the following code

SELECT 
    Q2.Item_Num,
    SWITCH
        (
        VAL(LEFT(("1" + Q2.Item_Num)),1)=1    ,          LEFT(VAL(Q2.Item_Number),5),
        VAL(LEFT(("1" + Q2.Item_Num)),2)=10   , "0"    + LEFT(VAL(Q2.Item_Number),4),
        VAL(LEFT(("1" + Q2.Item_Num)),3)=100  , "00"   + LEFT(VAL(Q2.Item_Number),3),
        VAL(LEFT(("1" + Q2.Item_Num)),4)=1000 , "000"  + LEFT(VAL(Q2.Item_Number),2),
        VAL(LEFT(("1" + Q2.Item_Num)),5)=10000, "0000" + LEFT(VAL(Q2.Item_Number),1)
        )
        AS Raw_Numb
FROM
    (
    [[SUBQUERY]]
    )
    AS Q2

In an attempt to get the results of

Item_Num    Raw_Num

00001ABC    00001
002ABC      002
3214        3214
87522ATT    87522
22P         22
04828P      04828
00421       00421

That however, results in the error The Microsoft Office Access database engine could not find the object ''. My search for a solution has taken me to this related question, which leads me to believe that somehow I am trying to reference a nameless object. This confusion is doubled when I know I can run

SELECT 
    Q2.Item_Num
FROM
    (
    [[SUBQUERY]]
    )
    AS Q2

And get the output of

Item_Num

00001ABC
002ABC
3214
87522ATT
22P
04828P
00421

Which leaves me with three interrelated questions: Is this just a result of a poorly executed SWITCH statement? Is this because I'm using a SWITCH statement in a manner that it can't be used in? If I'm only referencing Q2.Item_Num in the statement, how can I be searching for the object ''?

And a slightly off topic question for this Stack: Is there a better way to do what I'm trying to do?

See `OnlyDigits()` in the answer to a similar question: [Strip out non-numeric characters in SELECT](http://stackoverflow.com/a/12570564/77335). `OnlyDigits("002ABC")` would give you the string value *"002"*. — HansUp, Oct 13 '15 at 14:04
I imagined it was possible in VBA but I hadn't quite committed to solving it that way. You always seem to be the help I need, thanks @HansUp! — Fritz, Oct 13 '15 at 14:18
Also, I was able to create `NoDigits()` by changing `/d` to `/D` in the code of `OnlyDigits()`. Regular expressions are beautiful tool that I need to utilize more. — Fritz, Oct 13 '15 at 14:28
You're welcome, Fritz. If you use that function, make sure you get the changes I just made --- the latest version includes `Static objRegExp As Object` — HansUp, Oct 13 '15 at 14:28
I picked up the changes you made, thanks again! Have a good day! — Fritz, Oct 13 '15 at 14:34

Object not found when adding SWITCH statement to query

0 Answers0