splitting a field into two fields; one in ()

  • Thread starter Thread starter LisaK
  • Start date Start date
L

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?
 
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
 
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.
 
Back
Top