sorting columns with Access

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

Bob
Tue Jul 20 10:12:06 CDT 2004

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.

Chris
Tue Jul 20 18:05:20 CDT 2004

"shank" <shank@tampabay.rr.com> wrote in message
news:ur0$cimbEHA.712@TK2MSFTNGP11.phx.gbl...
> 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=utocmbiUDHA.2284%40TK2MSFTNGP09.phx.gbl

-Chris Hohmann

Bob
Wed Jul 21 08:17:30 CDT 2004

Chris Hohmann wrote:
> "shank" <shank@tampabay.rr.com> wrote in message
> news:ur0$cimbEHA.712@TK2MSFTNGP11.phx.gbl...
>> 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=utocmbiUDHA.2284%40TK2MSFTNGP09.phx.gbl
>
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.

Chris
Wed Jul 21 11:57:07 CDT 2004

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OeG3YUybEHA.2840@TK2MSFTNGP11.phx.gbl...
> Chris Hohmann wrote:
> > "shank" <shank@tampabay.rr.com> wrote in message
> > news:ur0$cimbEHA.712@TK2MSFTNGP11.phx.gbl...
> >> 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=utocmbiUDHA.2284%40TK2MSFTNGP09.phx.gbl
> >
> 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.