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

[Nano]

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...

 

[CJ_London]

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]

Yeah it was auto formatted by the excel document. Thank you for the tip.

 

[Nano]

I got it working, Thanks again!