Link to home
Start Free TrialLog 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
shieldsco
Flag of United States of America image

ASKER

receiving the following

User generated image
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
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
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of shieldsco
shieldsco
Flag of United States of America image

ASKER

same error
Avatar of shieldsco
shieldsco
Flag of United States of America image

ASKER

Thanks
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
Still not seeing he need for the list when you can just replace the punctuation and get the same results.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
I think Norie should get an accepted solution sine it was their IIF.  I just helped with the syntax.