Case Statement In Access?

[vbjohn]

I was wondering in the Query department... Is there a way that I could have this....

SELECT dbo_UPR30100HSE.GRWGPRN, dbo_UPR30100HSE.EMPLOYID, dbo_UPR00100HSE.SOCSCNUM, RTrim([LASTNAME])+', '+RTrim([FRSTNAME]) AS Name, dbo_UPR30100HSE.CHEKDATE,

(HRSWKD = TotalHrs > 520 THEN "520" ELSE TotalHrs/100)

FROM dbo_UPR30100HSE INNER JOIN dbo_UPR00100HSE ON dbo_UPR30100HSE.EMPLOYID = dbo_UPR00100HSE.EMPLOYID
WHERE (((dbo_UPR30100HSE.CHEKDATE) Between #1/1/2002# And #3/31/2002#));


Cause In SQL you can do this...


HRSWKD = CASE WHEN
ISNULL(((SELECT SUM(HOURSWKD) FROM UPR00401 WHERE PAYDATE BETWEEN '1/1/2002'AND '3/31/2002'AND
UPR00401.EMPLOYID = UPR00100.EMPLOYID AND PAYRCORD = 'HOURLY'
OR PAYDATE BETWEEN '1/1/2002'AND '3/31/2002'AND UPR00401.EMPLOYID = UPR00100.EMPLOYID AND
UPR00401.PAYRCORD = 'SALARY') / 100),0) > 520 THEN '520' ELSE
ISNULL(((SELECT SUM(HOURSWKD) FROM UPR00401 WHERE PAYDATE BETWEEN '1/1/2002'AND '3/31/2002'AND UPR00401.EMPLOYID =
UPR00100.EMPLOYID AND PAYRCORD = 'HOURLY' OR PAYDATE BETWEEN '1/1/2002'AND '3/31/2002'AND UPR00401.EMPLOYID =
UPR00100.EMPLOYID AND UPR00401.PAYRCORD = 'SALARY') / 100),0) END

 

[The_Doc_Man]

Look up either SWITCH or IIF in the Help files. Nested IIFs work if you only have a few choices; use SWITCH if you have too many things for you to remain comfortable with IIF.

 

[Pat Hartman]

Use the IIf() function.

(HRSWKD = TotalHrs > 520 THEN "520" ELSE TotalHrs/100)

Would be:
IIf(TotalHrs > 520, 520, TotalHrs/100) As HRSWKD