Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2006
    Posts
    3

    Unanswered: Can I use CASE in an MS ACCESS query?

    Hello - I'm new.

    I need convert the last character of a string .
    I was going to use a query with a case statement, but cannot get this to work in MS ACCESS.
    Here is what I am attempting:
    For this data:
    Table: Balances
    Field: ClosingBal
    Values:
    001235B
    012585E
    148526{

    And I want to use this:
    Select mid(ClosingBal,1,6) +
    case mid(Closingbal,7,1)
    when 'A' then '1'
    when 'B' then '2'
    when 'C' then '3'
    when 'D' then '4'
    when 'E' then '5'
    when 'F' then '6'
    when 'G' then '7'
    when 'H' then '8'
    when 'I' then '9'
    when '{' then '0'
    end

    query, to get:
    0012352
    0125855
    1485260

    but Access tells me:
    Sntax Error (missing operator)

    It seems to recognize that I am using a case statement, bt it wants something I am not giving it. Please help, thanks.

    --OLCB

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Short answer: No

    Long answer: You can fake it.

    You could use an iif() statement like so:

    iif(mid(Closingbal,7,1) = "{", 0, asc(mid(Closingbal,7,1)) - 64)

    Or you could create your own function in a public module like so:

    Public Function ConvertLast(CharIn) As String
    Dim CharOut As String
    Select Case charIn
    Case "A": CharOut = 1 'continue case statements here
    End Select

    ConvertLast = CharOut

    End Function
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2006
    Posts
    3

    thanks

    Thanks for the response.
    That's VB, right? I'll have to brush up.

    Just to be clear, though:
    I know the case steatement can work in SQL in other environments,
    but it cannot be used in simple SQL in MS ACCESS.

    Is that right?

    Thanks

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Correct. Nested iif()'s are as close as you can get.

    The iif() I provided will work in the query:

    SELECT mid(ClosingBal,1,6) & iif(mid(Closingbal,7,1) = "{", 0, asc(mid(Closingbal,7,1)) - 64)
    FROM yourTable
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Nested IIF's can do the job, however if you value your sanity bin 'em,- they are a bast!!!!d to debug, especailly if you are trying to do a complex task -IMHV they are fien for quick and dirty fixes, and great for realtively simple expressions but not appropriate foe the task you have

    For what you are outlining take up option 2 from Teddys original resposne - write a VB function to do what you want

    its not htat dissiniliar from your case statement

    the vb is

    public function <myfucntionname> (myparam) <myreturntype>
    select case UCASE(mid(<myparam>,7,1))
    case = "A": <myfnctionname>=1
    case = "b": <myfnctionname>=2
    ....etc
    case else: 'do nothing!
    end select
    end function

    to calll the function in the query
    select <myfunctioname>(<mycolumn>) as <myreturendvalue> from <mytablename>

    HTH

  6. #6
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    And within a query one can also use the Choose() and Switch() functions which might be handier than the IIf() function in this case...
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    but are choose and switch available in MS Access/JET?

  8. #8
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Yes, I've used them before for situations like this...
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I must have missed the incorporation of that feature into JET SQl... which versions of JET can you use Switch and Choose in?

  10. #10
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    I just tested in an Access 2000 database opened in Access 2002 and it seems like I've been able to use the functions since Access 97.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    good news, yet another <adjective deleted> feature Ive been wanting to use for years and didn't realise it wa there all along......

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Switch() wouldn't be a bad option. I was throwing this out there:

    Code:
    iif(mid(Closingbal,7,1) = "{", 0, asc(mid(Closingbal,7,1)) - 64)
    due to the relationship between the asc() values and his desired return. I second your suggestion of Switch() if he chooses to go a more explicit route though, good call.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yep - switch is good for these multi condition CASE equivelents. I''m sure you would have figured this out for yourself but... to simulate the ELSE clause, just pop in an expression that ways evaluates to true (e.g. 1=1).

    examples (all three equivelent):
    Code:
     
    CASE 
    WHEN MyCol = 10
    THEN "Ten"
    ELSE "Not Ten"
    END
     
    Iif(MyCol = 10, "Ten", "Not Ten")
     
    Switch(MyCol = 10, "Ten", 1=1, "Not Ten")
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2006
    Posts
    3

    thanks

    Thanks very much for the responses, everyone.
    Especially the examples of how to use the simple VB code and call it into the query. Great for a VB novice like myself.

    I didn't know about switch, but that might be what I need.

    Thanks again, I'm glad I joined the forum. You folks are great resources!

    --OLCB

  15. #15
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Great idea (1=1 for Else)!
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •