Text Field Break On Comma 1

[JHHPavel]

I am importing data from another application into MS Access; the name field in this application is in the format "Lastname, Firstname". The length of Lastname varies from one record to another.

My application has separate fields for Lastname and Firstname, so I want to break the name field being input into two sections, that before the comma and that after the comma. This could be done by examining the input data from left to right, one character at a time, until I get a comma. If the comma is at position X, I can extract what I want using text manipulator functions.

But that's the long way. This must be a common problem so I'm hoping there's a simple function in Access, like CommaBreak() that does it for me. Or maybe someone has a more efficient way than examining each characteer.

Thanks for any help you can give.

Paul
pintiha@jhmi.edu

 

[dhookom]

If all the field values are consistent, you can use Instr() to find the position of the comma within the value.
FirstName: Mid([FullName], Instr([FullName],",")+2)
LastName: Left([FullName], Instr([FullName],",")-1)

I don't believe you can do this directly while importing. You may need to append to a temporary table or field and then run an update or append query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.