Trouble with CASE Statement Syntax

[hellfire45]

Microsoft Access doesn't like this CASE statement syntax. It is fitted for SQL Server but I'm not quite sure how to modify it to fit in Access. Can anybody lend a hand? Thank you!

SELECT dbo_FCLIT.EntityID as 'File#', dbo_FCLIT.GroupNumNum as 'Numer of Actions', dbo_FCLIT.ActionType as 'Action Type', dbo_FCLIT.StartAction as 'Action StartDate', dbo_FCLIT.[End], dbo_Entities.Party3IDText as 'Client ID', 'Client Name' = 

CASE
WHEN dbo_Entities.Party3IDText ='245204' THEN 'SN SERVICING CORPORATION'
WHEN dbo_Entities.Party3IDText ='327599' THEN 'STATEBRIDGE'
ELSE 'Not Found'
END


FROM dbo_FCLIT
LEFT OUTER JOIN dbo_Entities ON dbo_FCLIT.EntityNum = dbo_Entities.EntityNum
Where (dbo_FCLIT.ActionType = 'Answer Affir Defense') AND ((dbo_Entities.Party3IDText ='245204') OR (dbo_Entities.Party3IDText = '327599'));

[June7]

Try nested IIf()

IIf(dbo_Entities.Party3IDText ='245204', 'SN SERVICING CORPORATION', IIf(dbo_Entities.Party3IDText ='327599', 'STATEBRIDGE', 'Not Found'))

[hellfire45]

Well it didn't error! But its not giving me what I want. That Calculated column is being titled "EXPR1" and its giving me nothing but zeros. Getting closer!

SELECT dbo_FCLIT.EntityID AS ['File#'], dbo_FCLIT.GroupNumNum AS ['Numer of Actions'], dbo_FCLIT.ActionType AS ['Action Type'], dbo_FCLIT.StartAction AS ['Action StartDate'], dbo_FCLIT.End, dbo_Entities.Party3IDText AS ['Client ID'], 'Client Name'=IIf(dbo_Entities.Party3IDText='245204','SN SERVICING CORPORATION',IIf(dbo_Entities.Party3IDText='327599','STATEBRIDGE','Not Found')) AS Expr1FROM dbo_FCLIT LEFT JOIN dbo_Entities ON dbo_FCLIT.EntityNum = dbo_Entities.EntityNum
WHERE (((dbo_FCLIT.ActionType)='Answer Affir Defense') AND ((dbo_Entities.Party3IDText)='245204' Or (dbo_Entities.Party3IDText)='327599'));

[ssanfu]

I think you have 'Client Name' in the wrong place. Try:

SELECT dbo_FCLIT.EntityID AS ['File#'], 
dbo_FCLIT.GroupNumNum AS ['Numer of Actions'], 
dbo_FCLIT.ActionType AS ['Action Type'], 
dbo_FCLIT.StartAction AS ['Action StartDate'], 
dbo_FCLIT.End, 
dbo_Entities.Party3IDText AS ['Client ID'], 
IIf(dbo_Entities.Party3IDText='245204','SN SERVICING CORPORATION',IIf(dbo_Entities.Party3IDText='327599','STATEBRIDGE','Not Found')) AS ['Client Name']

FROM dbo_FCLIT LEFT JOIN dbo_Entities ON dbo_FCLIT.EntityNum = dbo_Entities.EntityNum

WHERE (((dbo_FCLIT.ActionType)='Answer Affir Defense') AND ((dbo_Entities.Party3IDText)='245204' Or (dbo_Entities.Party3IDText)='327599'));

[June7]

Cannot set 'Client Name' = to anything.

You can put whatever you want for a name in place of EXPR1. Perhaps that's what you want Client Name to be? Do not include apostrophes in the alias field names. Should not use spaces and special characters/punctuation (underscore is exception) in any naming convention.

SELECT dbo_FCLIT.EntityID AS [FileNum], dbo_FCLIT.GroupNumNum AS [NumberOfActions], dbo_FCLIT.ActionType AS [ActionType], dbo_FCLIT.StartAction AS [ActionStartDate], dbo_FCLIT.End, dbo_Entities.Party3IDText AS [ClientID], IIf(dbo_Entities.Party3IDText='245204','SN SERVICING CORPORATION',IIf(dbo_Entities.Party3IDText='327599 ','STATEBRIDGE','Not Found')) AS ClientName FROM dbo_FCLIT LEFT JOIN dbo_Entities ON dbo_FCLIT.EntityNum = dbo_Entities.EntityNum
WHERE (((dbo_FCLIT.ActionType)='Answer Affir Defense') AND ((dbo_Entities.Party3IDText)='245204' Or (dbo_Entities.Party3IDText)='327599'));

[hellfire45]

You did it! Thanks! Very helpful indeed.