Split field into 2

[Roger Jones]

Hi

Is there a way that I can split a field (Contact_name) into 2 fields:
firstname, surname.

e.g if contact_name = "Roger Jones" , I want to split to firstname = Roger,
surname = Jones

any help appreciated.

--
Regards

Roger
<URL: http://www.roga.demon.co.uk >

[Lydia Gomeral]

Roger,

Provided that Contact_name is *always* firstname, space, surname, this will
work. Create two new fields in the table. I've named them SurName and
FirstName. Then do this:

Sub wynSplitNames()
Dim dbs As Database
Dim rst As Recordset
Dim lngLen As Long
Dim lngPos As Long
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Table3")
With rst
Do Until .EOF
.Edit
lngLen = Len(!Contact_name)
lngPos = InStr(1, !Contact_name, " ")
!SurName = Right(rst!Contact_name, lngLen - lngPos)
!FirstName = Left(!Contact_name, lngPos - 1)
.Update
.MoveNext
Loop
End With
End Sub
---
Lydia

Roger Jones wrote in message
<916408320.13908.0...@news.demon.co.uk>...

[Charles Maitland]

Roger
There are many of ways of doing this but here is just one of them....

Dim Firstname, Wholename as string
Dim X as Long

'Find the position of the first space in the name
X = Instr(Wholename, " ")

'Firstname is the charecters to the left of the first space
Firstname = Left$ (Wholename,X)

'Reduce the whole name by stripping out the charecters you have used for the
first name and leading spaces
Wholename = Trim(Right$(Wholename,X))

If you have only 2 names then Wholename is your last name otherwise repeat
the process.

I hope that this works for you.

Charlie

Roger Jones <ne...@roga.demon.co.uk> wrote in message
news:916408320.13908.0...@news.demon.co.uk...

[Steve Kuiper]

Probably not easily. You'll need to create the two new fields; then
use either a query, VBA code, or temp to populate the new fields from
the old field; and finally delete the old field.

The problem is the query / VBA part. Unless the contact names have
been entered with remarkable consistency, you're going to have trouble
automating this process. Look at the Instr(), Left(), and Right()
functions in help for guidance.

On Fri, 15 Jan 1999 13:51:36 -0000, "Roger Jones"
<ne...@roga.demon.co.uk> wrote:

>Hi
>
>Is there a way that I can split a field (Contact_name) into 2 fields:
>firstname, surname.
>
>e.g if contact_name = "Roger Jones" , I want to split to firstname = Roger,
>surname = Jones
>
>any help appreciated.

Steve Kuiper, CPA
Steve Kuiper & Co., P.A.
Altamonte Springs, FL
mpinet.net@kuipercpa (reverse these to respond by e-mail)

[Robert Farmer]

Sure seems like a lot of work when one make-table query will do the job.

fullname: currentfield
firstname:left$([currentfield],instr(currentfield," ")-1)
lastname:right$([currentfield],length([currentfield])-instr([currentfield],"
")))

RFarmer
Lydia Gomeral wrote in message
<#N#8vNJQ#GA....@uppssnewspub05.moswest.msn.net>...

>>Hi
>>
>>Is there a way that I can split a field (Contact_name) into 2 fields:
>>firstname, surname.
>>
>>e.g if contact_name = "Roger Jones" , I want to split to firstname =
Roger,
>>surname = Jones
>>
>>any help appreciated.
>>

[Lydia Gomeral]

Robert,

Given that I was a word processor in my previous life, I can type *really
fast*!
---
Lydia

Robert Farmer wrote in message
<#yPW0bJQ#GA....@uppssnewspub04.moswest.msn.net>...

[Robert Farmer]

Obvious evidence of a mis-spent youth. Personally, I couldn't get above
1500 keystrokes when I did data entry for the insurance company.

RFarmer
Lydia Gomeral wrote in message ...

[Roger Jones]

Thanx Charlie,

I'll have a look at Instr
--
Regards

Roger

Charles Maitland wrote in message <77njro$7it$1...@news8.svr.pol.co.uk>...

[Roger Jones]

Robert Farmer wrote in message ...

[snip]

Thanx Lydia & Robert, looks like Instr is the key. I'll have a go with the
make table query

[Tim Mills-Groninger]

Roger Jones wrote:
>

> Is there a way that I can split a field (Contact_name) into 2 fields:
> firstname, surname.
>
> e.g if contact_name = "Roger Jones" , I want to split to firstname = Roger,
> surname = Jones

What we (we, as in the wise-one who frequent this newsgroup) should do
is create the Access equivilent to Paradox's BreakApart() function.
BreakApart places the contents of a string into an array based on a
delimitting character. The string and the delimitting charater are the
only aurguments.

method pushButton(var eventInfo Event)
var
ar Array[] String ; Must be resizable
s String
endvar

s = "this is, a : delimited ? string"

s.breakApart(ar) ; breaks on spaces by default
ar.view()
{
ar = this
is,
a
:
delimited
?
string
}

I may take a crack at this at some point. Could be very usefull. Once
the string is in the array you parse based on the number of elements;
two element go into the first and last name fields, logic determines how
the rest get spread - isn't there a Dutch historian named "Van van der
Van?"

--
Tim Mills-Groninger
Information Technology Resource Center http://npo.net
Technolgy Resource Consortium http://www.igc.org/trc
NonProfit Times http://www.nptimes.com

[Dave Macmurchie]

This is such a common question that I offered a couple of functions that do
this to Dev Ashish for his Access Web site (http://home.att.net/~dashish/).
It turns out that I had made the same mistake that everyone in this thread
(and everyone else who asks the question) made, which was not to look over
Dev's site thoroughly first.

The Strings section includes a pair of functions from Microsoft that do the
trick. Look for "Parsing character separated string into individual
components"

Tim Mills-Groninger wrote in message <36A0B237...@igc.org>...