How do I...?

[Larry Baum]

I have a db I recived from my school district with parent names (stupidly)
maintained like:

Parent_Name
--------------
Doe, John
Doe, John & Jane
Doe, John & Deer, Jane

I want an update query that will fill four fields:
P1-First P1-Last P2-First P2-Last
---------------------------------------------
John Doe
John Doe Jane Doe
John Doe Jane Deer

[Larry Linson]

"Larry Baum" wrote

I have a db I recived from my school
district with parent names (stupidly)
maintained like:

Parent_Name
--------------
Doe, John
Doe, John & Jane
Doe, John & Deer, Jane

I want an update query that will fill four fields:
P1-First P1-Last P2-First P2-Last
---------------------------------------------
John Doe
John Doe Jane Doe
John Doe Jane Deer

If the names are consistently in the format you show, then the extraction
functions would be as follows. If "surprises" arise in the formatting...
missing spaces, different punctuation, titles such as "M.D.", etc, then you
will have to modify to make the functions work with those "exceptions ". I
didn't make any allowance, nor test, for such things as a missing First Name
of the first parent, or a "&" with nothing following

Public Function ExtractFirstLas t(Parent_Name As String) As String
ExtractFirstLas t = Left(Parent_Nam e, InStr(Parent_Na me, ",") - 1)
End Function
Public Function ExtractFirstFir st(Parent_Name As String) As String
Dim intStart As Integer
intStart = InStr(Parent_Na me, ",") + 2
If InStr(Parent_Na me, " &") <> 0 Then
ExtractFirstFir st = Mid(Parent_Name , intStart, InStr(Parent_Na me, "
&") - intStart)
Else
ExtractFirstFir st = Mid(Parent_Name , intStart, Len(Parent_Name ) -
intStart + 1)
End If
End Function

Public Function ExtractSecondLa st(Parent_Name As String) As String
Dim intStart As Integer
Dim intComma As Integer
intStart = InStr(Parent_Na me, "&")
If intStart = 0 Then
ExtractSecondLa st = ""
Else
intStart = intStart + 2
intComma = InStr(intStart, Parent_Name, ",")
If intComma = 0 Then
ExtractSecondLa st = ""
Else
ExtractSecondLa st = Mid(Parent_Name , intStart, intComma -
intStart)
End If
End If
End Function

Public Function ExtractSecondFi rst(Parent_Name As String) As String
Dim intStart As Integer
Dim intComma As Integer
intStart = InStr(Parent_Na me, "&")
If intStart = 0 Then
ExtractSecondFi rst = ""
Else
intStart = intStart + 2
intComma = InStr(intStart, Parent_Name, ",")
If intComma = 0 Then
ExtractSecondFi rst = Right(Parent_Na me, Len(Parent_Name ) -
intStart + 1)
Else
ExtractSecondFi rst = Right(Parent_Na me, Len(Parent_Name ) -
intComma - 1)
End If
End If

Best of luck with your project.

Larry Linson
Microsoft Access MVP

[Trevor Best]

On Thu, 25 Sep 2003 13:29:11 GMT in comp.databases. ms-access, "Larry
Baum" <la********@ver izon.net> wrote:

I have a db I recived from my school district with parent names (stupidly)
maintained like:

Parent_Name
--------------
Doe, John
Doe, John & Jane
Doe, John & Deer, Jane

I want an update query that will fill four fields:
P1-First P1-Last P2-First P2-Last
---------------------------------------------
John Doe
John Doe Jane Doe
John Doe Jane Deer

If it's that consistent then you need a two step pass to separate
first the people, then the names, you can use Split() to get the
people into an array (using the & as delimeter) then use Split again
on each element of the array (using , as delimeter) to separate the
names.

--
A)bort, R)etry, I)nfluence with large hammer.

[Pieter Linden]

"Larry Baum" <la********@ver izon.net> wrote in message news:<H4******* **********@nwrd dc02.gnilink.ne t>...

I have a db I recived from my school district with parent names (stupidly)
maintained like:

Parent_Name
--------------
Doe, John
Doe, John & Jane
Doe, John & Deer, Jane

I want an update query that will fill four fields:
P1-First P1-Last P2-First P2-Last
---------------------------------------------
John Doe
John Doe Jane Doe
John Doe Jane Deer

use InStr to find the ampersands (&). Deal with those first and split
to two
Last, First type fields. Then you can use Instr, Mid, Left, Right to
pull everything else out. Instr will return the position number of
the comma/ampersand. Then just go from there. But use a temp table
to do this or at least test it on sample data... otherwise you could
corrupt useful info!

HTH,
Pieter