Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •