count Like in Access

[Guest]

Hi guys,
I have in my Access database a field that shows multiple values per record.
For example, AAAA~BBBB~CCCC~DDDD. In a good database, AAAA, BBBB, CCCC and
DDDD would be placed as different records. However, the data that I got, they
are placed together, as one record separated by "~". In Excel it would be
like several values in one cell. Is it possible to run a count of how many
times each of those appear in the field? A field could have 1,000 records and
each record could have more than one combination (AAAA or AAAA~BBBB or
BBBB~CCCC~AAAA, etc). I want to run a report that will perform all counts at
one and not one at a time. Does anybody know if its possible? Thanks in
advance.

 

[Allen Browne]

Add the function below to a standard module.

Then type an expression like this into the Field row of query design:
HowMany([Field1])
replacing Field1 with the name of your field.

Function HowMany(varIn As Variant) As Long
If Not IsNull(varIn) Then
HowMany = UBound(Split(varIn, "~")) + 1
End If
End Function