Separating name (2002)

Topic

New Reply

I have a field with a name John H. Doe
I want to make a field with First name (FN) only and one with Last Name (LN)only No middle initial
What is the query code to do it?

Thanks

Paul

Reply | Quote

Replies

If all names are of this form, you can use:

FN: Left([FieldName],InStr([FieldName]," ")-1)

LN: Mid([FieldName],InstrRev([FieldName]," ")+1)

where FieldName is the name fo the field.

Note: InStrRev will only work in expressions in Access 2002 or later.

Reply | Quote

Thanks Hans – worked Great!

Paul

Reply | Quote

Just a Note, InStrRev also works in Access2000.

Reply | Quote

I know that InstrRev together with several other functions such as Replace and Round was introduced in Office 2000 VBA, but according to ACC2000: Cannot Use New Visual Basic for Application Functions as Expressions they can’t be used directly in queries and in the control source of text boxes. If InstrRev does work there, all the better.

Reply | Quote