Counting groups in a field

[Robert]

I have a field that contains a 10 digit # separated by comma’ sometimes this
field has 1 (10 digit#) and sometimes it has up to 8(10 digit#) I need to be
able to count the number of groupings it has any help would be appreciated.
Example
001902715984,001554000677,001339170985,001339172213,000807212774,000807200332
=6 Groupings (need help extracting this number from above example)
Counting the number of “,†is an option I just have no idea how to. Thanks
in advance.
robb

 

[Rick Brandt]

I have a field that contains a 10 digit # separated by comma'
sometimes this field has 1 (10 digit#) and sometimes it has up to
8(10 digit#) I need to be able to count the number of groupings it
has any help would be appreciated. Example
001902715984,001554000677,001339170985,001339172213,000807212774,000807200332
=6 Groupings (need help extracting this number from above example)
Counting the number of "," is an option I just have no idea how to.
Thanks in advance.
robb

Are you in a position to change this data structure? A database field should
never contain more than a single piece of data. Even if you solve whatever
technical hurdle you face now you will be continually running into roadblocks
requiring more and more work-arounds as long as your data is structured like
this.

That aside, a user defined function could use any number of methods to determine
how many values are in your field. For example feeding the field value to the
Split() function would build an array with each value in an array position.
Then you just determine how many postions that created array has and you know
how many values there are and by subtracting 1 you know how many commas there
are.

 

[Robert]

rick, the field is imported from an excell spread sheet monthly. the field is
not utlized in any way except for the counting of the groups. can we count
the "," (and add 1) instead of creating a split with an array for each value?

 

[Douglas J. Steele]

The Split() is just one example of determining how many values there are
and thus how many commas there are. You can also create a function that
loops through all the characters and counts the commas...

Function CommaCount(FieldIn As String) As Byte

Dim i As Byte

For i = 1 To Len(FieldIn)
If Mid(FieldIn, i, 1) = "," Then CommaCount = CommaCount + 1
Next i

Probably simpler to check the length of the string, replace all commas in
the string with zero-length strings and see how much shorter the string it.

Function CommaCount(FieldIn As String) As Byte

CommaCount = Len(FieldIn) - Len(Replace(FieldIn, ",", vbNullString))

Exit Function

 

[Van T. Dinh]

You only nrrd to use the UBound() function to get the count. For example"

UBound(Split([YourField], ",")) + 1

will give you the number of groups in each Field value.