MS Access Syntax Error

shieldsco
shieldsco used Ask the Experts™
I'm using the following code and receiving Access error code:

Error

SELECT
CASE 
WHEN [tblConsolidated].[Appeal Category L3] IN
('180 MEDICAL INC',
'180 MEDICAL INC',
'180 Medical Inc.',
'180 Medical, Inc',
'180 MEDICAL, INC.')
THEN '180 MEDICAL INC'
Else [tblConsolidated].[Appeal Category L3];

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant
As far as I know you can't use CASE WHEN in Access SQL, what you could try instead is an Iif statement.

Iif([tblConsolidated].[Appeal Category L3] IN ('180 MEDICAL INC', '180 MEDICAL INC', '180 Medical Inc.', '180 Medical, Inc', '180 MEDICAL, INC.'), '180 MEDICAL INC'
, [tblConsolidated].[Appeal Category L3])

Open in new window


Or perhap simpler.
IIf(UCase(Replace(Replace(Replace([tblConsolidated].[Appeal Category L3], ',', ''), '.', ''), ' ', '')) = '180MEDICALINC', '180 MEDICAL INC', [tblConsolidated].[Appeal Category L3])

Open in new window

receiving the following

Image
Most Valuable Expert 2012
Distinguished Expert 2018
Do you need all the checks?

See if this works:
SELECT  ucase(replace(replace([Appeal Category L3],'.',''),',','')) from tblConsolidated;
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018
>>receiving the following
Make sure you have the FROM in your select.

It worked for me:
SELECT Iif([tblConsolidated].[Appeal Category L3] IN ('180 MEDICAL INC', '180 MEDICAL INC', '180 Medical Inc.', '180 Medical, Inc', '180 MEDICAL, INC.'), '180 MEDICAL INC'
, [tblConsolidated].[Appeal Category L3])
from tblConsolidated
;

Open in new window

same error
Thanks
Most Valuable Expert 2012
Distinguished Expert 2018
Still not seeing he need for the list when you can just replace the punctuation and get the same results.
Most Valuable Expert 2012
Distinguished Expert 2018
I think Norie should get an accepted solution sine it was their IIF.  I just helped with the syntax.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial