1. Parsing fields-Counts

I have a table in access 97 and I need to parse a memo field in order to count the number of names between numeric values.
(e.g)
1001 Bob Brown, Tim Black, Billy Joe Green, 1002 Suzy White, Carol Ann Purple, Samantha Amy "Sammy" Orange.

I imagine we start at the numeric value(strPos) go to the next numeric value and count the commas to the left- that would give you the total number of entries per number
Can I just plug this into a query and get a count?

2. Re: Parsing fields-Counts

I once wrote a User Defined Function for someone who wanted to sort an alpanumberic field and have the numbers sort properly. It parses numbers and letters into seperate alternating fields. That might work for you. Just count the commas. I'll see if I can find it.

3. Re: Parsing fields-Counts

This is turning out to be a real data nightmare!
I wish they would've listened to me a year ago about stricter data control.

Other factors:

There are multiple entries per record that could contain a comma in the middle of
each substring [e.g. Billy Bob "Bubba" Brown,III]

There is no guarantee that there is a space between each name
[e.g. Tammy Black,Terry Brown]

I hope that this sample exported Excel table better illustrates what I am up against.
<img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

4. Re: Parsing fields-Counts

Sorry it took so long to get back to you. Things just got the better of me this afternoon.

The UDF has two arguements: The string and a position number.

For the string 1001 Bob Brown, Tim Black, Billy Joe Green, 1002 Suzy White, Carol Ann Purple, Samantha Amy "Sammy" Orange

strparse(str,1) will return 1001
strparse(str,2) will return Bob Brown, Tim Black, Billy Joe Green,
strparse(str2,3) will return 1002
and so forth

Here is the code:

Public Function strParse(strInput As String, intGroup As Integer)
Dim str1 As String
Dim intCntPosition1 As Integer, intLen As Integer, intCntGroup As Integer
Dim intCntPosition2 As Integer
intLen = Len(strInput)
intCntPosition1 = 2
intCntGroup = 1
intCntPosition2 = 1
If intLen = 1 Then
If intGroup = 1 Then
strParse = strInput
Exit Function
Else
strParse = Null
Exit Function
End If
End If
Do Until intCntPosition1 > intLen + 1
If IsNumeric(Mid(strInput, intCntPosition2, 1)) <> IsNumeric(Mid(strInput, intCntPosition1, 1)) Then
strParse = Mid(strInput, intCntPosition2, intCntPosition1 - intCntPosition2)
If intGroup = intCntGroup Then
Exit Function
Else
intCntGroup = intCntGroup + 1
intCntPosition2 = intCntPosition1
End If
End If
intCntPosition1 = intCntPosition1 + 1
Loop
If intGroup > intCntGroup Then
strParse = Null
Else
strParse = Mid(strInput, intCntPosition2, intCntPosition1 - intCntPosition2)
End If
End Function

Good Luck

5. Re: Parsing fields-Counts

This is awsesome!!
In psuedocode-if you will...
what steps are necessary to take this code and write it to another table?

I mean, I can follow the steps, but what kind of procedure would call this UDF?
More importantly what would the procedure arguments be, and how do I call it?

As a result I want to write the number , then all of the subsequent names for each number.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•