extracting first name and surname

[ClayMac]

Hi,

I have been sent a file of customer contacts. They are held in a single field and have the format firstname + surname. So that I can load this data into a enterprise database system, I need to be able to extract surname and first name into to separate fields.

Any ideas on how I achieve this through an Access query?

Many thanks

Clay

 

[ColinEssex]

You presumably have a space between the names.

You use Instr() to find the space (because its always in a different place depending on the forename length), then use the Left() and Right() functions to locate the forename (up to the space) and the surname (after the space)

Col

 

[ClayMac]

Colin,

Thanks for your post. I just knew that there had to be a built in function but couldn't find anything suitable using the office assistant.

I will give it a try.

Many thanks

Clay