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 ExtractFirstLast(Parent_Name As String) As String
ExtractFirstLast = Left(Parent_Name, InStr(Parent_Name, ",") - 1)
End Function
Public Function ExtractFirstFirst(Parent_Name As String) As String
Dim intStart As Integer
intStart = InStr(Parent_Name, ",") + 2
If InStr(Parent_Name, " &") <> 0 Then
ExtractFirstFirst = Mid(Parent_Name, intStart, InStr(Parent_Name, "
&") - intStart)
Else
ExtractFirstFirst = Mid(Parent_Name, intStart, Len(Parent_Name) -
intStart + 1)
End If
End Function

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

Public Function ExtractSecondFirst(Parent_Name As String) As String
Dim intStart As Integer
Dim intComma As Integer
intStart = InStr(Parent_Name, "&")
If intStart = 0 Then
ExtractSecondFirst = ""
Else
intStart = intStart + 2
intComma = InStr(intStart, Parent_Name, ",")
If intComma = 0 Then
ExtractSecondFirst = Right(Parent_Name, Len(Parent_Name) -
intStart + 1)
Else
ExtractSecondFirst = Right(Parent_Name, 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********@verizon.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********@verizon.net> wrote in message news:<H4*****************@nwrddc02.gnilink.net>...

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