What is the CASE statement syntax?

[wilmoslee]

Dear All,

May i know what is the syntax to writer the CASE statment in MS ACCESS?
Is the MS ACCESS can support for this CASE function?

Thanks.

Regards,
Wil

[Gibbs]

Case
When [argument] Then [Result] End as [column name]

Or
Case
When [Argument] Then [Result] Else [Result] End as [Column name]

However, Access SQL does not really work with the Case Else, you must use

IIF(condition, truepart, falsepart)

You can nest the IIFs( Up to seven) by following this pattern

IIF(Condition, truepart,IIF(Condition, truepart,IIF(condition, truepart... and so forth...just make sure to close all the parenthesis at the end

You first CASE is the first condition, the first true part is the desired THEN output, the first nested IIF would be the ELSE part

You can use the "build" option by right clicking in the column selection area of the query window or the criteria row of the query window..or type directly into SQL by switching to that view.

[wilmoslee]

Thanks you so much, i try to work it out and will reply to you soon!!
Thanks.

Rgds,
WilmosLee.