copy and paste part of a field

[Jen0dorf]

Hi

not sure if this is possible but thought I'd ask

Have been given a table with 5400 records

fields are:

number
mlink
surname
index
inscription

the table has no unique fields and no index

The person who created the table placed the surname in the inscription field in capitals ie TOMES, Tom Fredk 3.2.37 a 76, Jessie Mary 3.1.56 a 96


where TOMES is the surname

is there any way to copy the TOMES part and paste it into the surname field

ie at the moment I have

PLot Number Index Mlink Surname Inscription

Tc 1001 8m n3 TRIPP, Edward Hill



I want it to have TRIPP in the Surname field

Can this be done using vba or SQL??

I did think of creating a copy of the table under another name and then use an update query to update the surname field from the inscription field and then to reduce it's length but that seemed a bit clumsy.

I've attached a copy of the table should anyone care to look

thanks

Ian

[ssanfu]

See if this helps.

First, I created a query to filter out records that obviously did not have a surname at the beginning of the field and did not have a comma after the surname.
In code, I checked if the surname equaled the uppercase version of the surname. I also had to check for prefixes such as "Mc", Mac", "De",....

What is left are the inscriptions you manually have to handle.

[Jen0dorf]

Hi

really appreciate the assist.

it does exactly what I wanted

I shall now digest the " queries" to see how you achieved it and then move on the module which will be a new experience

Cheers

Ian