Link to home
Create AccountLog in
Avatar of shieldsco
shieldscoFlag for United States of America

asked on

MS Access Syntax Error

I'm using the following code and receiving Access error code:

User generated image

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

Avatar of Norie
Norie

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

Avatar of shieldsco

ASKER

receiving the following

User generated image
Do you need all the checks?

See if this works:
SELECT  ucase(replace(replace([Appeal Category L3],'.',''),',','')) from tblConsolidated;
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
same error
Thanks
Still not seeing he need for the list when you can just replace the punctuation and get the same results.
I think Norie should get an accepted solution sine it was their IIF.  I just helped with the syntax.