splitting a field into two fields; one in ()

[LisaK]

I have a Name field in my database that is LastName,FirstName(dept). I have
figured out how to split last name and first name but I don't know how to
leave out the department name that is in () after the first name. Any
suggestions?

 

[techrat]

Quick and Dirty way to get the field down to just LastName,Firstname
is to use the following as an expression in a query:

LastFirst:Left([Name],Instr([Name],"(")-1)

This should give you all of the characters to the left of the opening
parenthesis. You could then apply your method for splitting the First
and Last Names.


HTH

 

[Beetle]

For the Last Name;

Left([YourField],InStr([YourField],",")-1)

For the First Name without the dept;

Mid([YourField],InStr([YourField],",")+1,InStr([YourField],"(")-(InStr([YourField],",")+1))

BTW - hopefully your field is not actually called Name, as this is a
reserved word in Access and should not be used as a field or
control name.