Help with a query to pull data from within a string

I have an imported table that has 1 data field ('name') with the following records
American Stairs (AMS)
CherryWood (CHW)
DoRay Furnishings (DRN)

In a query, I need to separate the 3 character designation out from between the parenthesis... how can I do that in a query please?

The parenthesis always start 1 space after the name, and the designation is always three characters long (i.e. AMS, CHW)

Thanks

Microsoft Access

Last Comment

snyperj

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

3/16/2015

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.

Michael Fowler

3/16/2015

I think this should do it but cannot test at the moment.

SELECT Mid(name, Instring(name, "(") + 1, Instring(name, ")") + 1)

Open in new window

You may be better to just get the name from the database and then extract the 3 character designation

Rey Obrero (Capricorn1)

3/16/2015

this will handle more than 3 letters in the parentheses

select Trim (Replace(Replace(Mid([Name], InStr([Name], "(")), "(", ""), ")", ""))
from tablex

Michael Fowler

3/16/2015

And a second look at mine and realised it will not work, should have been

SELECT Mid(name, Instring(name, "(") + 1, 3)

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

Gustav Brock

3/17/2015

And the minimum code:

NME: Left(Right([FieldName],4),3)

/gustav

snyperj

3/17/2015

ASKER

thanks to all, but this was first and all I needed

Certified Expert Program

Credly Partnership

Udemy Partnership