CODEWITHSUNDEEP
sqlms-accesscase
Jeff Brady
Jeff Brady

Reputation: 1498

SQL / MS Access - why isn't this CASE working?

I'm writing a query that determines which price to use based on a user-defined date named "USERDATE". When I run this, I get a "syntax error (missing operator) in expression". I want the price selected to be the "UNIT_AMT" which is used later on in the query.

SELECT MDDB.NDC, CARRIER.GROUP_ID, [USERDATE] AS USERDATE, MDDB.CODE, CARRIER.DUR_MU_R, CARRIER.FEE_BR, CARRIER.FEE_GR, CARRIER.AWP_BR, CARRIER.AWP_GR, CARRIER.DUR_MU_M, CARRIER.FEE_BM, CARRIER.FEE_GM, CARRIER.AWP_BM, CARRIER.AWP_GM, MDDB.AWP_UNIT_AMT, 

CASE
WHEN USERDATE >= AWP_EFF_DATE THEN AWP_UNIT_AMT
WHEN USERDATE >= AWP_1ST_OLDEST_EFF_DATE AND USERDATE < AWP_EFF_DATE THEN AWP_1ST_OLDEST_UNIT_AMT
WHEN USERDATE >= AWP_2ND_OLDEST_UNIT_AMT AND USERDATE < AWP_1ST_OLDEST_UNIT_AMT THEN AWP_2ND_OLDEST_UNIT_AMT
WHEN USERDATE >= AWP_3RD_OLDEST_UNIT_AMT AND USERDATE < AWP_2ND_OLDEST_UNIT_AMT THEN AWP_3RD_OLDEST_UNIT_AMT
WHEN USERDATE >= AWP_4TH_OLDEST_UNIT_AMT AND USERDATE < AWP_3RD_OLDEST_UNIT_AMT THEN AWP_4TH_OLDEST_UNIT_AMT
WHEN USERDATE >= AWP_5TH_OLDEST_UNIT_AMT AND USERDATE < AWP_4ST_OLDEST_UNIT_AMT THEN AWP_5TH_OLDEST_UNIT_AMT
ELSE AWP_UNIT_AMT
END AS UNIT_AMT,

((([UNIT_AMT]*[QTY])*((100+[AWP_GR])/100))+[DUR_MU_R]+[FEE_GR]) AS [GR PRICE], 
((([UNIT_AMT]*[QTY])*((100+[AWP_BR])/100))+[DUR_MU_R]+[FEE_BR]) AS [BR PRICE], 
((([UNIT_AMT]*[QTY])*((100+[AWP_GM])/100))+[DUR_MU_M]+[FEE_GM]) AS [GM PRICE], 
((([UNIT_AMT]*[QTY])*((100+[AWP_BM])/100))+[DUR_MU_M]+[FEE_BM]) AS [BM PRICE]
FROM MDDB_MASTER AS MDDB, CARRIER_GROUP AS CARRIER
WHERE (((MDDB.NDC)="41520091574") AND ((CARRIER.GROUP_ID)="709608"));

Thank you for your time and help!

Upvotes: 1

Views: 117

Answers (1)

HansUp
HansUp

Reputation: 97101

Since Access SQL does not support CASE ... WHEN, you could use nested IIf() expressions. However, with so many conditions, that nested IIf() would be challenging. In this case you may find Switch() easier to use.

Switch(
    USERDATE >= AWP_EFF_DATE, AWP_UNIT_AMT,
    USERDATE >= AWP_1ST_OLDEST_EFF_DATE AND USERDATE < AWP_EFF_DATE, AWP_1ST_OLDEST_UNIT_AMT,
    USERDATE >= AWP_2ND_OLDEST_UNIT_AMT AND USERDATE < AWP_1ST_OLDEST_UNIT_AMT, AWP_2ND_OLDEST_UNIT_AMT,
    USERDATE >= AWP_3RD_OLDEST_UNIT_AMT AND USERDATE < AWP_2ND_OLDEST_UNIT_AMT, AWP_3RD_OLDEST_UNIT_AMT,
    USERDATE >= AWP_4TH_OLDEST_UNIT_AMT AND USERDATE < AWP_3RD_OLDEST_UNIT_AMT, AWP_4TH_OLDEST_UNIT_AMT,
    USERDATE >= AWP_5TH_OLDEST_UNIT_AMT AND USERDATE < AWP_4ST_OLDEST_UNIT_AMT, AWP_5TH_OLDEST_UNIT_AMT,
    True, AWP_UNIT_AMT
) AS UNIT_AMT

Upvotes: 2

Related Questions