By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,743 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

How do I...?

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"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


Nov 12 '05 #2

P: n/a
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.
Nov 12 '05 #3

P: n/a
"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
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.