CASE statement

[sepia]

I get the following error for the following CASE statement;
"Incorrect syntax near the keyword 'WHEN'". Can anyone help me out?

Select Hourly_Wage = CASE when Employ_Hours_Work_Per_Week_Num = 0 THEN 0
ELSE
CASE Compensation_Unit_Type
WHEN('Hourly') THEN Compensation_Amt
WHEN('Weekly') THEN Compensation_Amt/Employ_Hours_Work_Per_Week_Num
WHEN('Annual') THEN Compensation_Amt/(Employ_Hours_Work_Per_Week_Num * convert(varchar(2),50)
WHEN('Annually') THEN Compensation_Amt / (Employ_Hours_Work_Per_Week_Num * convert(varchar(2),50)
ELSE 0 END
END

 

[Maquis]

Hmm, I'm not sure what language you wrote that in, but it doesn't look like VBA. You can look up the Select Case statement in Access help to get more detail but here is roughly the syntax for a case statement in VBA.

Select Case Hourly_Wage
Case "Hourly"
do stuff
Case "Annual"
do stuff
Case "Weekly"
do stuff
Case else
do more stuff
End Select
Maq B-)
<insert witty signature here>

 

[JerryDennison]

I'm not aware of any keyword &quot;WHEN&quot; for use with the Select Case statement. Syntax for Select Case is as follows:

Select Case testexpression
[Case expressionlist-n
[statements-n]] ...
[Case Else
[elsestatements]]
End Select

You can also use the keyword &quot;To&quot; or &quot;Is&quot;

Case 1 To 5
Case Is > MaxNumber

 

[tlbroadbent]

CASE is valid ANSI SQL or SQL Server but not in Access. You'll need to use IIF in a query instead of the ANSI SQL CASE. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[JerryDennison]

Select Case is valid VBA code in Access.

 

[tlbroadbent]

Jerry,

I agree but the construct of the CASE statement in the original post is not a VBA SELECT CASE statement. It appears to be a SQL CASE statement. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.