About Pricing Community Teams Start Free Trial

shieldsco

asked on 12/3/2019

MS Access Syntax Error

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

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

Microsoft AccessSQL

Last Comment

slightwv (䄆 Netminder)

8/22/2022 - Mon

Norie

12/3/2019

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

shieldsco

12/3/2019

ASKER

receiving the following

slightwv (䄆 Netminder)

12/3/2019

Do you need all the checks?

See if this works:
SELECT ucase(replace(replace([Appeal Category L3],'.',''),',','')) from tblConsolidated;

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

12/3/2019

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

shieldsco

12/3/2019

ASKER

same error

shieldsco

12/3/2019

ASKER

Thanks

slightwv (䄆 Netminder)

12/3/2019

Still not seeing he need for the list when you can just replace the punctuation and get the same results.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

slightwv (䄆 Netminder)

12/3/2019

I think Norie should get an accepted solution sine it was their IIF. I just helped with the syntax.

Plans and Pricing

Certified Expert Program

Credly Partnership

Udemy Partnership