sorting columns with Access

[shank]

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_as c' 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_as c' THEN Description END DESC,
CASE 'C' WHEN 'type_asc' THEN Type END DESC,
CASE 'C' WHEN 'label_asc' THEN Label END DESC

[Bob Barrows [MVP]]

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',O rderNo,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 Hohmann]

"shank" <sh***@tampabay .rr.com> wrote in message
news:ur******** *****@TK2MSFTNG P11.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_as c' 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_as c' 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...TNGP09.phx.gbl

-Chris Hohmann

[Bob Barrows [MVP]]

Chris Hohmann wrote:

"shank" <sh***@tampabay .rr.com> wrote in message
news:ur******** *****@TK2MSFTNG P11.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_as c' 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_as c' 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...TNGP09.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 Hohmann]

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:Oe******** ******@TK2MSFTN GP11.phx.gbl...

Chris Hohmann wrote:

"shank" <sh***@tampabay .rr.com> wrote in message
news:ur******** *****@TK2MSFTNG P11.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_as c' 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_as c' 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...TNGP09.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.