Counting

[Guest]

I have a text field containing a number of model numbers and they are
separated by a comma and a space. Is there any way to count the number of
model numbers in the field and add up all the numbers of records? Any
suggestion to this issue is appreciated.

Thanks,

Ray

 

[Allen Browne]

Ray, you are probably aware that this is a very bad way to store data in
Access, so I will spare you the lecture about creating another table and
putting the relationed records there. Of course it would then be child's
play to to query, count and sum the values, with a basic Totals query.

If you can't do that for some reason, the far less efficient approach would
be to write a VBA function that uses Split() to parse the text field into an
array, and then loop through the items of the array between LBound() and
UBound() to sum them. The count of items would be:
UBound(Split([YourTextField], ", ")) + 1

 

[Ray]

Allen,

Thanks for your useful suggestion.

Ray

Ray, you are probably aware that this is a very bad way to store data in
Access, so I will spare you the lecture about creating another table and
putting the relationed records there. Of course it would then be child's
play to to query, count and sum the values, with a basic Totals query.

If you can't do that for some reason, the far less efficient approach
would be to write a VBA function that uses Split() to parse the text field
into an array, and then loop through the items of the array between
LBound() and UBound() to sum them. The count of items would be:
UBound(Split([YourTextField], ", ")) + 1

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a text field containing a number of model numbers and they are
separated by a comma and a space. Is there any way to count the number
of
model numbers in the field and add up all the numbers of records? Any
suggestion to this issue is appreciated.

Thanks,

Ray