separating a string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

janetager@yahoo.com - 24 Dec 2006 18:22 GMT

I have a field that has names in it in the format "last name, first
name". I want code that will split this field for me at the comma so I
can make 2 separate fields for last name and first name. Thanks.

Reply to this Message

Rick Brandt - 24 Dec 2006 18:27 GMT

> I have a field that has names in it in the format "last name, first
> name". I want code that will split this field for me at the comma so
> I can make 2 separate fields for last name and first name. Thanks.

FirstName: Mid([FieldName], InStr(1, [FieldName], ", ")+2)

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

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Jeff Smith - 24 Dec 2006 19:34 GMT

Just incase some records don't have a space
LastName = Left([FieldName], Instr([FieldName], ",")-1)
FirstName = Trim(Mid([FieldName], Instr([FieldName], ",")+1))

>I have a field that has names in it in the format "last name, first
> name". I want code that will split this field for me at the comma so I
> can make 2 separate fields for last name and first name. Thanks.

Reply to this Message

Keith Hutchison - 24 Dec 2006 21:12 GMT

> I have a field that has names in it in the format "last name, first
> name". I want code that will split this field for me at the comma so I
> can make 2 separate fields for last name and first name. Thanks.

dim data as string
dim fields() as string
dim last as string
dim first as string

data = "Last name, first name"
fields = split( data, ",")
last = trim( fields(0) )
if ubound( fields ) > 0 then
first = trim(fields(1))
end if

Cheers

Keith

Reply to this Message