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

    Parsing table-Counts

    I have a table and I need to parse a 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.
    How could this be done in Word97?
    Thanks for any help given.

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Parsing table-Counts

    Hi Jazman,

    This sounds doable (and I recall something similar from the old Lounge), but can you clarify what it is we're parsing and where it's going to be found?:

    Is the data in Access or is already in a Word, and if so, is it in a Word table?
    Are we parsing just one 'field' or do we need to loop through a bunch of them?
    We do we need to do with the name count results?

    Gary

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing table-Counts

    Appreciate the help but I can't even get it out of Access.
    I've done alot of character manipulation in Word using patterns and such, but it's a memo field and the text is extensive.
    Everytime I try to export it, I truncate the field contents.
    Thanks for the help tho.... <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  4. #4
    Francis Winter
    Guest

    Re: Parsing table-Counts

    Well, you could use the built-in Split function to divide it all into words, and then start counting, placing the results in an array. Something roughly like:

    <pre>'Tests the GetCount function
    Sub TestRead()
    Const INDEX As Long = 0
    Const NAMES As Long = 1

    Const SAMPLE As String = "1001 Bob Brown, Billy Joe Green, 1002 Suzy White, " & _
    "Carol Ann Purple, Samantha Amy Sammy Orange 1003 Tim Black, Bob Brown, Billy Joe " & _
    "Green, 1004 Suzy White, Carol Ann Purple, Bob Brown, Samantha Amy Sammy Orange"

    Dim avarRecs As Variant
    Dim iRec As Long
    'Create an array of record numbers and their name counts
    avarRecs = GetCounts(SAMPLE)
    'List the contents of the array

    For iRec = 0 To UBound(avarRecs, 2)
    If IsEmpty(avarRecs(INDEX, iRec)) Then Exit For
    Debug.Print avarRecs(INDEX, iRec), avarRecs(NAMES, iRec)

    Next

    End Sub

    'Reads a string, creating an array of record numbers and their name counts
    'Note that the array is returned in a variant
    Function GetCounts(Numbered As String) As Variant
    Const BLOCK As Long = 20
    Const COMMA As Long = 44
    Const INDEX As Long = 0
    Const NAMES As Long = 1

    Dim strWord As String
    Dim strRecNum As String

    Dim avarWords As Variant
    Dim avarRecs() As Variant

    Dim lngRec As Long
    Dim lngCount As Long
    Dim lngMax As Long
    Dim iWord As Long

    Dim blnInName As Boolean

    If Len(Trim$(Numbered)) = 0 Then Exit Function
    'Prepare an array to hold the results
    lngMax = BLOCK
    lngRec = -1
    ReDim avarRecs(INDEX To NAMES, 0 To lngMax)

    'Segment string by spaces,
    avarWords = Split(Numbered, " ")
    'loop through "words"
    For iWord = 0 To UBound(avarWords)
    strWord = avarWords(iWord)
    'Start counting names after each number
    If IsNumeric(strWord) Then

    'If we we were mid name when the number was hit
    'increment the name counter for the prev. record
    If blnInName Then lngCount = lngCount + 1


    'If needed, expand the array which holds the results,
    If lngRec > lngMax Then
    lngMax = lngMax + BLOCK
    ReDim Preserve avarRecs(INDEX To NAMES, 0 To lngMax)
    End If

    'and store the results for the prev. record
    If Len(strRecNum) Then
    avarRecs(INDEX, lngRec) = strRecNum
    avarRecs(NAMES, lngRec) = lngCount
    End If
    'get the new record number, and reset the name counter
    lngRec = lngRec + 1
    strRecNum = strWord
    lngCount = 0
    'no name words yet read
    blnInName = False
    ElseIf Asc(Right$(strWord, 1)) = COMMA Then
    lngCount = lngCount + 1
    blnInName = False
    Else
    blnInName = True
    End If
    Next
    'if we were in a name, waiting for a comma,
    'when the whole string ended, update and store
    'results for the last record

    If blnInName Then
    'If needed, expand the array which holds the results,
    If lngRec > lngMax Then
    lngMax = lngMax + BLOCK
    ReDim Preserve avarRecs(INDEX To NAMES, 0 To lngMax)
    End If

    lngCount = lngCount + 1
    avarRecs(INDEX, lngRec) = strRecNum
    avarRecs(NAMES, lngRec) = lngCount
    End If
    GetCounts = avarRecs
    End Function</pre>


Posting Permissions

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