Create field

[mohd21uk via AccessMonster.com]

I am trying to split a field called UserName into two seperate fields. The
field contains records in the following format Doe, John Where Doe is the
surname and John is the First Name. I would like to create two new fields in
Table called Surname and FirstName in the employee table with Firstname would
be generated using the following expression:

Orignial Entry in [Names]: "Doe, John"
Returned by Expression: John
Expression: Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(1, _
[Names]," "))

How can I edit this expression so that it creates the field First Name in the
employee table.

Many thanks in advance

 

[Al Camp]

I'd suggest using the comma in the Names string as the "locator", not the space.
Also, all Names must have in the format "Surname,(space)LastName"

Run an Update Query against the table...
For Surname
= Left([Names], (InStr([Names], ",") -1))
For FirstName
= Mid([Names], (InStr([Names], ",") +1))

 

[Martin]

If you are working in a form, you could make an event macro that is triggered
after you type something into the Names field. Create your First Name field
in the table and add it as a control to the form. Then go to properties for
the original Names field control and choose the AfterUpdate event and click
on the three-dot builder button. Using your expressions:

Sub First_Name_AfterUpdate()
Me.First_Name = Right(Trim([Names]), etc, etc
End Sub