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

Robert wrote:
> 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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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?

"Rick Brandt" wrote:

> Robert wrote:
> > 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.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>
>
>

Robert wrote:
> 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?

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


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

"Rick Brandt" <(E-Mail Removed)> wrote in message
news:QCsqh.1157$(E-Mail Removed)...
> Robert wrote:
>> 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?
>
> 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



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

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.

--
HTH
Van T. Dinh
MVP (Access)




"Robert" <(E-Mail Removed)> wrote in message
news:A1E28EBD-66AF-48DD-832D-(E-Mail Removed)...
> 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?
>