Conditional Ifs in Access SQL?

[Adam Clauss]

I need to perform a SQL operation where depending on the value of column A,
either the value of column B or column C might be returned.

Something like:

SELECT If(ColA = 1, ColB, ColC) As Total FROM mytable;

Result would be one column, Total. If column A's value was 1, the value of
total would be ColB, else ColC.

Is this possible?

Thanks!

Adam Clauss
(e-mail address removed)

 

[Mike Labosh]

I need to perform a SQL operation where depending on the value of column A,

either the value of column B or column C might be returned.

Iif() Function. it stands for "immediate if". You pass it an expression,
something to return if the expression is true, and something to return if
the expression is false. So all you need to do is add an i:

SELECT Iif(ColA = 1, ColB, ColC) As Total FROM mytable;
--
Peace & happy computing,

Mike Labosh, MCSD

Feed the children!
Save the whales!
Free the mallocs!

 

[Van T. Dinh]

Close. You need to use the IIf() function (Immediate If).

SELECT IIf(ColA = 1, ColB, ColC) As Total FROM mytable

 

[Adam Clauss]

Ahh... perfect. Thanks to both of you for your help.

 

[Adam Clauss]

OK, problem related to this now. Is it not possible to then sort on this
total field?
Ex:

SELECT Iif(ColA = 1, ColB, ColC) As Total FROM mytable ORDER BY Total;

Its throwing an error whenever I try to do this.

Thanks,
Adam Clauss
(e-mail address removed)

 

[Douglas J. Steele]

Unfortunately, you can't create dynamic SQL like that.

Try this instead:

SELECT ColB As Total FROM mytable
WHERE ColA = 1
UNION
SELECT ColC As Total FROM mytable
WHERE ColA <> 1
ORDER BY 1

Note that should ColA contain any Null values, those values will be excluded
from the resultant recordset. If you want to include them, use:

SELECT ColB As Total FROM mytable
WHERE ColA = 1
UNION
SELECT ColC As Total FROM mytable
WHERE ColA <> 1 OR ColA IS NULL
ORDER BY 1

or

SELECT ColB As Total FROM mytable
WHERE ColA = 1
UNION
SELECT ColC As Total FROM mytable
WHERE Nz(ColA) <> 1
ORDER BY 1