Need function to reverse LName, FName (1 Viewer)

[Angel69]

Hi,

I have a table with data that is imported from another application. The employee name is showing up as LName, FName. What function can I use to make these FName LName with no comma? I know I may have to do a two step to separate the two then concatanate but not sure how to do it. Thanks

UTA: I figured out LName: Trim(Left([ApprovedByFullName],InStr([ApprovedByFullName],",")-1)) to pull the last name minus the comma. How do I pull the first name?

 

[bob fitz]

Can you give us some example data

 

[Angel69]

DIAZ, MARY
GOMEZ, LENNY
PARKS, LEE

Are some examples I need converted.

 

[Brianwarnock]

using instr to find the, you can use MID to extract the firstname, mid without a length extracts the data to the right of the start point.

Brian

 

[MSAccessRookie]

Looks like you made a good start with the Last Name. In a similar manner, you can use the RIGHT() and InstrRev() Functions to get the First Name from the end of the String. Look at those two Functions and get back to us if you need any further assistance.

-- Rookie

UPDATE: Looks like Brian posted another alternative before I could post my reply. I am sure that either solution would work for you. Take a look at both of them so that you can understand their benefits.

 

[vbaInet]

I'm with Brian on this one.

However, it sounds like your database should already have the first name and last name parts in a table somewhere. If it doesn't then you might want to reconsider your table design.

 

[Angel69]

Thanks everyone! I ended up with:

FName: Trim(Mid([ApprovedByFullName],InStr([ApprovedByFullName],",")+1))

 

[Brianwarnock]

Glad we could all help, and what I like is that you did not need it coding by us but did it yourself.

Brian