Analysing field

[samer]

I have this data in my table:
Restaurants, Inc. (BJRI)
Cheesecake Factory in (CAKE)
P.F. Chang's China Bistro (PFCB)
Panera Bread Company (PNRA)
Rubio's Restaurants, Inc. (RUBO)
Abercrombie & Fitch Co. (ANF)
Aeropostale, Inc. (ARO)

I want to select the data which between brackets and put in a new field ,
Like this :

BJRI
CAKE
PFCB
PNRA
RUBO
ANF
ARO
How to do that ?

 

[WayneRyan]

samer,

Current Table:

tblBusinessOld
==============
Business - Text

New Table:

tblBusinessNew
==============
BusinessID - AutoNumber
BusinessName - Text
BusinessStockName - Text

Then just run the following query:

Insert Into tblBusinessNew (BusinessName, BusinessStockName)
Select Mid(Business, 1, Instr(1, Business, "(") - 1),
       Mid(Business, Instr(1, Business, "(") + 1, Instr(1, Business, ")") - 1
From   tblBusinessOld

ID Business                  Stock
== ========================= =====
 1 Restaurants, Inc.         BJRI
 2 Cheesecake Factory in     CAKE
 3 P.F. Chang's China Bistro PFCB
 4 Panera Bread Company      PNRA
 5 Rubio's Restaurants, Inc. RUBO
 6 Abercrombie & Fitch Co.   ANF
 7 Aeropostale, Inc.         ARO

Then anytime you reference one of them, use their ID number. Access makes
sure that it's unique.

Wayne

 

[samer]

samer,

Current Table:

tblBusinessOld
==============
Business - Text

New Table:

tblBusinessNew
==============
BusinessID - AutoNumber
BusinessName - Text
BusinessStockName - Text

Then just run the following query:

Insert Into tblBusinessNew (BusinessName, BusinessStockName)
Select Mid(Business, 1, Instr(1, Business, "(") - 1),
       Mid(Business, Instr(1, Business, "(") + 1, Instr(1, Business, ")") - 1
From   tblBusinessOld

ID Business                  Stock
== ========================= =====
 1 Restaurants, Inc.         BJRI
 2 Cheesecake Factory in     CAKE
 3 P.F. Chang's China Bistro PFCB
 4 Panera Bread Company      PNRA
 5 Rubio's Restaurants, Inc. RUBO
 6 Abercrombie & Fitch Co.   ANF
 7 Aeropostale, Inc.         ARO

Then anytime you reference one of them, use their ID number. Access makes
sure that it's unique.

Wayne

Thank you a lot .
note : i did add ) after the last 1

 

[WayneRyan]

samer,

No wonder I felt unbalanced today!

Wayne