Extracting part of a text value from a table… (1 Viewer)

Nano

Registered User.
Local time
Today, 15:01
Joined
May 14, 2012
Messages
91
Hello I am working on a new project database. This time I need to compile a list of authors from a excel file I have imported into a new database.

I have all the data I need in my table, however I have run into small issue. I only want the main author to be queried from the author field on my table. See the picture for an example. It is the last author in the field. I am not sure how to do this without manually removing the other names from the field. I have 3000 of these to change. Is there a way to have Access do this for me?

See edit...
 

Attachments

  • example.JPG
    example.JPG
    18.2 KB · Views: 96
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 20:01
Joined
Feb 19, 2013
Messages
16,737
Looks like you need to redesign your DB and have authors in a separate table in a 1 to many relationship with the publication (that is the correct way of doing it).

A simpler alternative would be to have two fields one populated with your main author and the other populated with the 'sub' authors.

To separate out your authors may not be too difficult, it looks like thery are separated with a semicolon.

If this is always the case then the following code can be adapted to find the main author

MainAuthor=mid(authors,instrrev(authors,";")+2)

this will return

Hawker, Craig J.
Chan, Siew Hwa
 

Nano

Registered User.
Local time
Today, 15:01
Joined
May 14, 2012
Messages
91
Yeah it was auto formatted by the excel document. Thank you for the tip.
 

Nano

Registered User.
Local time
Today, 15:01
Joined
May 14, 2012
Messages
91
I got it working, Thanks again!
 

Users who are viewing this thread

Top Bottom