Count the total number of integer in a list

[sansiang]

Hi,

Have been looking for solution else where but not able to find it.
In one table, I have a few fields. One of the field is "ItemSequence" and another one is "TotalPcs".

"ItemSequence" is where user key in the sequence number for one or more item.
5 example for possible content of "ItemSequence" is as following :
1) 7
2) 4,6,9
3) 5-9
4) 3,5,9, 23-25
5) 3-5, 8-10

"TotalPcs" is the total number of items key in to "ItemSequence". For the 5 example above, the related "TotalPcs" should be as following:
1) 1 (1 item, which is item 7 alone)
2) 3 (3 item which is item 4, 6 and 9)
3) 5 (5 item which is item 5, 6, 7, 8 and 9)
4) 6 (6 item which is item 3, 5, 9, 23, 24 and 25 )
5) 6 (6 item, which is item 3, 4, 5, 8, 9 and 10)

For time being, the user have to count manually to get the "TotalPcs". I wonder is there a way to calculate the "TotalPcs" by programming?

Thanks for any idea.

 

[PeterF]

With the split function you can do a custum function like:

Function CountItemSequence(ItemSequence As String) As Integer
    Dim varSplitItemSequence
    Dim varSplit


    varSplitItemSequence = Split(ItemSequence, ",")
    For Each varsplit In varSplitItemSequence
        If varsplit Like "*-*" Then
            CountItemSequence = CountItemSequence + Mid(varsplit, (InStr(1, varsplit, "-") + 1)) - Left(varsplit, (InStr(1, varsplit, "-") - 1)) + 1
        Else
            If Not IsNull(varsplit) Then
                CountItemSequence = CountItemSequence + 1
            End If
        End If
  
    Next
End Function

Depending on how consequent te field is filled you wil need some kind of error trapping.

 

[sansiang]

Wow !
Looks inspiring...
Will give it a try.

 

[MarkK]

I'd use Split() again on the hyphen.

Function CountItems(ByVal vList) As Integer
    Dim var
    Dim vRange
    Dim count As Integer
    
    vList = Split(vList, ",")
    For Each var In vList
        count = count + 1
        If InStr(var, "-") Then
            vRange = Split(var, "-")
            count = count + vRange(0) - vRange(1)
        End If
    Next
    CountItems = count
    
End Function

 

[PeterF]

I'd use Split() again on the hyphen.

I agree, when I hit submit I tought the same
But with a range the first item is also an entry so your code would be one short.

count = count + vRange(0) - vRange(1) [COLOR="Red"][B]+ 1[/B][/COLOR]

 

[sansiang]

PeterL and Labbolt, both of you are wonderful.

Your suggestion seem so simple and neat.
I certainly have learn a new trick today.

 

[MarkK]

The code I posted has already added that one because it counts one for every loop, whether the item is a single number or a range.

 

[sansiang]

Already try it out.
Work beautifully.
By the way, it should be vRange(1) -vRange(0) , and not the other way round.