Reverse Concatenate a field?

  • Thread starter Thread starter Ryan Hallett
  • Start date Start date
R

Ryan Hallett

Hello, I have a field that is called full_name. The
contents are as such:

Doe, John
Doe, Jane
..
..
..


I would like to split the field into 2 fields, first
name, and last name. Is there any way of doing this in
Access? Perhaps there is a SQL function for this? I
read about the "Split" function in the online help, but
Access doesn't recognize the Split function, it's like it
doesn't exist. I'm using Access 2003.

Thanks,

Ryan
 
Ryan Hallett said:
Hello, I have a field that is called full_name. The
contents are as such:

Doe, John
Doe, Jane
.
.
.


I would like to split the field into 2 fields, first
name, and last name. Is there any way of doing this in
Access? Perhaps there is a SQL function for this? I
read about the "Split" function in the online help, but
Access doesn't recognize the Split function, it's like it
doesn't exist. I'm using Access 2003.

Thanks,

Ryan
Click to expand...

A quick way to this is to create a couple of query fields like the
following.

Surname: Left([Full_Name], InStr(1, [FullName], ",") - 1)
FirstName: Mid([Full_Name], InStr(1, [FullName], ",") + 2)

The InStr returns the position of the Comma in the name, the left and
Mid then cut the string into pieces. Of course this only works if the
"," is present, you will need to take steps to counter this.

Note that the "Split" function returns an Array that can be used in VB
code. It would be a good way of writing a proper splitting function.

Regards,
Kevin Rollo
Perth, Western Australia
 
Back
Top