SQl Syntax error on Case ()

[Trudye]

I am trying to convert this query from Oracle to Access. I am getting an error
Syntax Error 'Missing Operator" in query expression 'Case When...'

Does anyone have any idea what is wrong with the syntax in this query?

Thanx,
Trudye

SELECT R.RETAIL_CNTR_NUM, I.VIN_NUM
, I.LEASE_ACCT_NUM, I.BUS_SRC_CD, MID(I.VIN_NUM,1,8),  VIN8, 
CASE WHEN I.DIST_CHAN_CD='9' AND UCASE(I.NMC_MDL_CD) NOT IN ('NISSA','INFIN') THEN N.MODEL_CD 
WHEN I.DIST_CHAN_CD='9' AND UCASE(I.NMC_MDL_CD) IN('NISSA','INFIN') THEN MID(N.MODEL_CD,1,3) & 'X' & MID(RTRIM(N.MODEL_CD),5,1) 
ELSE MID(I.NMC_MDL_CD,1,3) & 'X' & MID(I.NMC_MDL_CD,5,1)
END, 
MDL_CD, I.VIN_YEAR, 
CAST(CASE WHEN MID(I.FULL_MDL_YR3,1,1)='0' THEN '20' ELSE '19' END & MID(I.FULL_MDL_YR3,1,2) AS NUMBER) MDL_YR
, UCASE(CASE I.DIST_CHAN_CD WHEN '1' THEN 'Nissan' WHEN '2' THEN 'Infiniti' 
ELSE N.MAKE_DEC END) 
MAKE, Ucase(CASE I.DIST_CHAN_CD WHEN '9' THEN N.MODEL_DESC 
ELSE M.MDL_LINE_DESC END) 
MODEL, I.CUR_RPC_ID, 
CASE WHEN I.EXCS_WTEAR_AMT+I.MISS_PART_AMT>=2000 OR I.EXCS_WTEAR_AMT=1 THEN 'X' ELSE NULL END 
EXCS_DMG, R.BK_FLG, R.RETAIL_RCVRY_ST_CD
FROM SMM.VEH_EVENTS_ENH_T I, SMM.REPO_BANKRUPTCY_T R, SMM.SMM_NONNNA_VEH_T N, SMM.MDL_LINE_T M
WHERE EXISTS

 

[PHV]

In JetSQL use IIf instead of CASE WHEN and don't use CAST.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886