sorting columns with Access

Discussion in 'ASP General' started by shank, Jul 20, 2004.

  1. shank

    shank Guest

    Is the CASE statement allowed when querying an Access database?
    Assuming so, what is the correct syntax?
    thanks

    SELECT OrderNo, Description, Type, Label
    FROM Stock
    WHERE Manuf = 'M'
    ORDER BY
    CASE 'C' WHEN 'orderno_asc' THEN OrderNo END ASC,
    CASE 'C' WHEN 'description_asc' THEN Description END ASC,
    CASE 'C' WHEN 'type_asc' THEN Type END ASC,
    CASE 'C' WHEN 'label_asc' THEN Label END ASC,
    CASE 'C' WHEN 'orderno_asc' THEN OrderNo END DESC,
    CASE 'C' WHEN 'description_asc' THEN Description END DESC,
    CASE 'C' WHEN 'type_asc' THEN Type END DESC,
    CASE 'C' WHEN 'label_asc' THEN Label END DESC
     
    shank, Jul 20, 2004
    #1
    1. Advertising

  2. shank wrote:
    > Is the CASE statement allowed when querying an Access database?


    You could have found out much quicker by using Access online help, but no,
    CASE is not supported by JetSQL.

    > Assuming so, what is the correct syntax?
    > thanks
    >
    > SELECT OrderNo, Description, Type, Label
    > FROM Stock
    > WHERE Manuf = 'M'
    > ORDER BY
    > CASE 'C' WHEN 'orderno_asc' THEN OrderNo END ASC,


    ?? What is 'C'? As written, it's a literal string that will never be equal
    to 'orderno_asc'.


    You have to use the VBA Iif() function, also known as "Immediate If".
    Assuming 'C' is actually intended to represent a parameter, here is how it
    would look:

    Iif([C] = 'orderno_asc',OrderNo,0) ASC,
    ....

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Jul 20, 2004
    #2
    1. Advertising

  3. "shank" <> wrote in message
    news:ur0$...
    > Is the CASE statement allowed when querying an Access database?
    > Assuming so, what is the correct syntax?
    > thanks
    >
    > SELECT OrderNo, Description, Type, Label
    > FROM Stock
    > WHERE Manuf = 'M'
    > ORDER BY
    > CASE 'C' WHEN 'orderno_asc' THEN OrderNo END ASC,
    > CASE 'C' WHEN 'description_asc' THEN Description END ASC,
    > CASE 'C' WHEN 'type_asc' THEN Type END ASC,
    > CASE 'C' WHEN 'label_asc' THEN Label END ASC,
    > CASE 'C' WHEN 'orderno_asc' THEN OrderNo END DESC,
    > CASE 'C' WHEN 'description_asc' THEN Description END DESC,
    > CASE 'C' WHEN 'type_asc' THEN Type END DESC,
    > CASE 'C' WHEN 'label_asc' THEN Label END DESC
    >


    In addition to the IIF function Bob mentioned, you may also want to
    investigate the SWITCH function. The nested IIF functions could get pretty
    hairy in this particular situation. Bob, haven't we had this conversation
    before? :)

    http://groups.google.com/groups?selm=

    -Chris Hohmann
     
    Chris Hohmann, Jul 21, 2004
    #3
  4. Chris Hohmann wrote:
    > "shank" <> wrote in message
    > news:ur0$...
    >> Is the CASE statement allowed when querying an Access database?
    >> Assuming so, what is the correct syntax?
    >> thanks
    >>
    >> SELECT OrderNo, Description, Type, Label
    >> FROM Stock
    >> WHERE Manuf = 'M'
    >> ORDER BY
    >> CASE 'C' WHEN 'orderno_asc' THEN OrderNo END ASC,
    >> CASE 'C' WHEN 'description_asc' THEN Description END ASC,
    >> CASE 'C' WHEN 'type_asc' THEN Type END ASC,
    >> CASE 'C' WHEN 'label_asc' THEN Label END ASC,
    >> CASE 'C' WHEN 'orderno_asc' THEN OrderNo END DESC,
    >> CASE 'C' WHEN 'description_asc' THEN Description END DESC,
    >> CASE 'C' WHEN 'type_asc' THEN Type END DESC,
    >> CASE 'C' WHEN 'label_asc' THEN Label END DESC
    >>

    >
    > In addition to the IIF function Bob mentioned, you may also want to
    > investigate the SWITCH function. The nested IIF functions could get
    > pretty hairy in this particular situation. Bob, haven't we had this
    > conversation before? :)
    >
    >

    http://groups.google.com/groups?selm=
    >

    Guilty. I forgot about Switch again. However, in this case I do not think it
    is appropriate unless all those columns have the same datatype.

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Jul 21, 2004
    #4
  5. "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Chris Hohmann wrote:
    > > "shank" <> wrote in message
    > > news:ur0$...
    > >> Is the CASE statement allowed when querying an Access database?
    > >> Assuming so, what is the correct syntax?
    > >> thanks
    > >>
    > >> SELECT OrderNo, Description, Type, Label
    > >> FROM Stock
    > >> WHERE Manuf = 'M'
    > >> ORDER BY
    > >> CASE 'C' WHEN 'orderno_asc' THEN OrderNo END ASC,
    > >> CASE 'C' WHEN 'description_asc' THEN Description END ASC,
    > >> CASE 'C' WHEN 'type_asc' THEN Type END ASC,
    > >> CASE 'C' WHEN 'label_asc' THEN Label END ASC,
    > >> CASE 'C' WHEN 'orderno_asc' THEN OrderNo END DESC,
    > >> CASE 'C' WHEN 'description_asc' THEN Description END DESC,
    > >> CASE 'C' WHEN 'type_asc' THEN Type END DESC,
    > >> CASE 'C' WHEN 'label_asc' THEN Label END DESC
    > >>

    > >
    > > In addition to the IIF function Bob mentioned, you may also want to
    > > investigate the SWITCH function. The nested IIF functions could get
    > > pretty hairy in this particular situation. Bob, haven't we had this
    > > conversation before? :)
    > >
    > >

    >

    http://groups.google.com/groups?selm=
    > >

    > Guilty. I forgot about Switch again. However, in this case I do not think

    it
    > is appropriate unless all those columns have the same datatype.


    The return value of the SWITCH function is variant so I think we're ok.
     
    Chris Hohmann, Jul 21, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. helpful sql
    Replies:
    0
    Views:
    818
    helpful sql
    May 19, 2005
  2. Replies:
    5
    Views:
    599
  3. Replies:
    4
    Views:
    941
  4. =?Utf-8?B?YmVub2l0?=

    Read CSV - string Columns - Int columns

    =?Utf-8?B?YmVub2l0?=, May 8, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    436
    =?Utf-8?B?YmVub2l0?=
    May 8, 2006
  5. Jacksm
    Replies:
    2
    Views:
    855
    Paul Chalekian
    Nov 21, 2006
Loading...

Share This Page