Move the last data in a new field

[Dimitris]

Hello in a field of a table there is data entered and at the end of most
entries there is a bracket with some letters inside. (AB)
What I need is to remove the brackets and the letters included into a new
field. So if the data in the field is "John Smith (GM)" I want John Smith to
remain in the field but (GM) to move to the new field.
Note that some entries do not have brackets at all, but the ones that do
have them, are always at the end if that makes any difference.

Can someone help?
Thanks
Dimitris

 

[Allen Browne]

Use Instr() to locate the brackets, and Mid() to pick up the rest of the
field.

Use an update query (Update on Query menu in query design) to update your
other field.

 

[Dimitris]

Thanks for your reply, but I don't understand what to do.
I have limited knowledge in Access.
Can you please tell me what exacly I should do.

 

[Duane Hookom]

How about providing some real names of "a field" "a table" "a new field"?
Is the "a new field" empty?

Have you ever used an update query?

 

[Dimitris]

Table name is: ICP1 field name is: THESI new field is: PST
The New Field PST is empty.
I have used a new field before when some people helped me. But I don't know
what Instr() is and what to write in the Mid()
Sorry but I am new in access.

Dimitris

 

[Duane Hookom]

Make a backup copy of your table and try:

UPDATE ICP1
SET THESI = Trim(Left(THESI, Instr(THESI,"(")-1))),
PST = Trim(Mid(THESI, Instr(THESI,"("))))
WHERE THESI Like "*)"

 

[Dimitris]

Thank You

Make a backup copy of your table and try:

UPDATE ICP1
SET THESI = Trim(Left(THESI, Instr(THESI,"(")-1))),
PST = Trim(Mid(THESI, Instr(THESI,"("))))
WHERE THESI Like "*)"