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_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 Barrows [MVP]]

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

 

[Chris Hohmann]

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/[email protected]

-Chris Hohmann

 

[Bob Barrows [MVP]]

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/[email protected]

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

 

[Chris Hohmann]

http://groups.google.com/[email protected]. 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.