Count of specific character in a text

[shahaba]

I have a list of texts and I need to find out the number of times each character shows up.

Example in the text "76DC4-34" the character '4' shows up twice. Is there a query that for this instance if I provide the text and character I'm looking for it will give the number of occurences as a result or if there is no such query how do I go about getting the result I want?

I assume I'm going to have to repeat a process 36 times (10 for each numeric digit and 26 for each letter, other characters in the text don't matter)

[ADezii]

@shahaba
I do not think that there is an easy Method of doing what you describe, but the following Function should point you in the right direction. It will count the Total Number of occurrences of each Number contained within the String passed to it:

Public Function fCountCharacters(strString As String) As String
Dim intLenOfString As Integer
Dim intCounter As Integer
Dim strResult As String
Dim intNumOf0s As Integer
Dim intNumOf1s As Integer
Dim intNumOf2s As Integer
Dim intNumOf3s As Integer
Dim intNumOf4s As Integer
Dim intNumOf5s As Integer
Dim intNumOf6s As Integer
Dim intNumOf7s As Integer
Dim intNumOf8s As Integer
Dim intNumOf9s As Integer
 
If Len(strString) = 0 Then Exit Function
 
intLenOfString = Len(strString)
 
For intCounter = 1 To intLenOfString
  Select Case Mid$(strString, intCounter, 1)
    Case "0"
      intNumOf0s = intNumOf0s + 1
    Case "1"
      intNumOf1s = intNumOf1s + 1
    Case "2"
      intNumOf2s = intNumOf2s + 1
    Case "3"
      intNumOf3s = intNumOf3s + 1
    Case "4"
      intNumOf4s = intNumOf4s + 1
    Case "5"
      intNumOf5s = intNumOf5s + 1
    Case "6"
      intNumOf6s = intNumOf6s + 1
    Case "7"
      intNumOf7s = intNumOf7s + 1
    Case "8"
      intNumOf8s = intNumOf8s + 1
    Case "9"
      intNumOf9s = intNumOf9s + 1
  End Select
Next
 
strResult = "Number of 0s: " & Format$(intNumOf0s, "00") & vbCrLf
strResult = strResult & "Number of 1s: " & Format$(intNumOf1s, "00") & vbCrLf
strResult = strResult & "Number of 2s: " & Format$(intNumOf2s, "00") & vbCrLf
strResult = strResult & "Number of 3s: " & Format$(intNumOf3s, "00") & vbCrLf
strResult = strResult & "Number of 4s: " & Format$(intNumOf4s, "00") & vbCrLf
strResult = strResult & "Number of 5s: " & Format$(intNumOf5s, "00") & vbCrLf
strResult = strResult & "Number of 6s: " & Format$(intNumOf6s, "00") & vbCrLf
strResult = strResult & "Number of 7s: " & Format$(intNumOf7s, "00") & vbCrLf
strResult = strResult & "Number of 8s: " & Format$(intNumOf8s, "00") & vbCrLf
strResult = strResult & "Number of 9s: " & Format$(intNumOf9s, "00") & vbCrLf
  fCountCharacters = strResult
End Function

Debug.Print fCountCharacters("76DC4-34")

OUTPUT:

Number of 0s: 00
Number of 1s: 00
Number of 2s: 00
Number of 3s: 01
Number of 4s: 02
Number of 5s: 00
Number of 6s: 01
Number of 7s: 01
Number of 8s: 00
Number of 9s: 00

[missinglinq]

Here's a function that is a little more compact. It uses the Replace() function, so you'll have to be using Access 2000 or later for it to work. It covers everything you've specified, 0-9 and A -Z.

Public Function CountAllCharacters(ParsedString As String) As String
Dim ReportString As String
ReportString = ""
 
For x = 48 To 90
 If x > 57 And x < 65 Then GoTo Skip2MeLoop
 cnt = Len(ParsedString) - Len(Replace(ParsedString, Chr(x), ""))
 
If cnt <> 0 Then
 If ReportString = "" Then
   ReportString = "Number of " & Chr(x) & "'s: " & cnt
 Else
  ReportString = ReportString & vbCrLf & "Number of " & Chr(x) & "'s: " & cnt
 End If
End If
Skip2MeLoop:
Next x
 
CountAllCharacters = ReportString
 
End Function
 
 

This will give you an output, for the sample string 76DC4-34 of

Number of 3's: 1
Number of 4's: 2
Number of 6's: 1
Number of 7's: 1
Number of C's: 1
Number of D's: 1

If you need to show zero for all the characters that do not appear, a simple modification will do that.

Welcome to Bytes!

Linq ;0)>

[ChipR]

Linq, that's brilliant!

[FishVal]

Not bad, not bad.

However I don't think iterating ASCII values is a good idea.
I suppose a better way is to:

• Get the first character.
• Replace this character with nothing throughout the string and get length difference.
• Return to Step 1 until the string is empty.

This gives a more efficient code and allows (I'm almost sure though haven't checked this) to work with unicode strings.

Regards,
Fish.

[ADezii]

@missinglinq
Nice job Linq! Your code is definitely more compact, efficient, practical, portable, digestible, etc., etc., etc.

[ADezii]

@FishVal
Hello FishVal, just out of sheer curiosity, why don't you think that iterating ASCII values is a good idea? Would there be that much of a difference iterating ASCII Values as opposed to their Character Representations?

[FishVal]

@ADezii

• This is inefficient because not all characters are expected to be in a string being processed.
• This is completely unacceptable for unicode strings.

[ADezii]

@FishVal
Thanks Fish, couldn't the ChrW() Function be used to return a String containing a Unicode character as well as the Standard ASCII Characters from a String?

[FishVal]

It certainly could, but there is no reason to search a string for all unicode characters.

[missinglinq]

Thank you for pointing this out, FishVal, but until you entered the thread I don't remember anyone talking about unicode strings. Did I miss something?

Of course, taking the first letter of the string and replacing all instances of it, making a note of how many instances there were, then repeating this thru the string, would leave you with

1) A final string that listed the characters in the order that they appeared, not 0-9 and A-Z order

2) Having to account for repeating characters so that they weren't counted twice.

3) Having to account for characters other than 0-9 and A-Z, which is all that the OP stated they were concerned with.

Linq ;0)>

[FishVal]

@missinglinq
:D Well. The initial question was not about counting all characters at all.

Of course, taking the first letter of the string and replacing all instances of it, making a note of how many instances there were, then repeating this thru the string, would leave you with

1) A final string that listed the characters in the order that they appeared, not 0-9 and A-Z order

Results array sorting would not take more time than running Replace() function for all possible characters.

2) Having to account for repeating characters so that they weren't counted twice.

They will not since they are excluded from string when found.

3) Having to account for characters other than 0-9 and A-Z, which is all that the OP stated they were concerned with.

Don't you like to go slightly beyond of what you guess about OP's question? :D

Kind regards,
Fish.

[OldBirdman]

The original question is to count the number of occurrences for a given character. Function would return a single value, from 0 to n, the length of the original string. If this is a field in a query, then SELECT tbl.StringToTest AS [Match]. . . fCountOccur([Match],"A") AS CountA . . . FROM tbl

Public Function fCountOccur(strSource As String, strMatch As String) As String
Dim iCount As Integer
Dim iPosition As Integer
    iCount = 0
    For iPosition = 1 To Len(strSource)
        If Mid(strSource, iPosition, 1) = strMatch Then iCount = iCount + 1
    Next
    fCountOccur = Format(iCount, "000")
End Function
 

One query could do all 36 conditions. Design grid would have 36 fields beyond original table. They might be CountA: fCountOccur([Match],"A"), CountB: fCountOccur([Match],"B"), etc.
These 36 fields could be concatinated into one long string, such that the count for 0's is in positions 1-3; the count for 1's in positions 4-6; etc. therefore, there are mid(strLongString,n*3+1,3) n's, n from 0 to 9. Letters would be n=Asc(letter)-55. Concatination could be in query or with an enclosing loop in function, in which case 2nd argument would not be used as inner loop would use ascii value from outer loop.

OldBirdman

[ADezii]

Sorry guys, but I still feel that Linq's approach, or a variation thereof, is the way to go on this one!

[missinglinq]

No, ADezii! You don't think we should try to see how #$%^&* complicated we could make this? I mean, what if some of the strings were in Klingon? Would it work then? Or in Esperanto and encrypted? Would it work then?

Linq ;0)>

[FishVal]

Fathers,

Actually (now it is related to original question) I would not use a query with 36 calculated fields. Characters to count (in Klingon, Esperanto or even in Shakespare's Talk) could be stored in an additional table. This table could be joined using cartesian join with the target table to produce all possible combinations of [text] and [character] which are used to calculate character ocurence count. Thus obtained dataset could be used as a source for crosstab query.

Query: qryCount

SELECT tbl.txt, tblChars.txtChar,
Len(tbl.txt)-Len(Replace(tbl.txt, tblChars.txtChar, "",1,-1,1)) AS lngCount
FROM tbl, tblChars;
 

Query: qryCrosstab

TRANSFORM First(qryCount.lngCount) AS lngCount
SELECT qryCount.txt
FROM qryCount
GROUP BY qryCount.txt
PIVOT qryCount.txtChar;
 

[OldBirdman]

We don't know, and nobody has asked, why this is needed. For the example given, text = "76DC4-34", I can't think of any reason why this might be needed. I assumed English (26 letters) and not Klingon, Russian, or Arabic.
I think the way to proceed depends on this answer. Is it 1) a One-Time study, 2) a query or table field(s) even if temporary, or 3) a calculated control? When shahaba says "I assume I'm going to have to repeat a process 36 times ... ", does this mean he will be working with 36 values, or can he reduce it to one value, say the largest? If the answer is 1), then a list might be appropriate. If 2) or 3), this has to be reduced to a single number or string. These require different solutions.

[FishVal]

:D

Aim? Who does care. Just fire.

[NeoPa]

I think the OP forgot to come back (Either that, or they were frightened off by all the high-faluting "discussions").

In truth though, it's always interesting to get the points of view of other experts (Yes OB - That includes you too, regardless of tag), even when they are not strictly required for the original question.

[OldBirdman]

NeoPa, as the only non-expert here, I am glad you appreciate my input.
"shahaba" is a Newbie (1 post), and needs to be invited back into this discussion.
shahaba, can you give a few more details, such as how you are going to use this number. Is it a field in a table, an unbound control on a form or report, a variable in a VBA formula, a one-time special study, or ...?
You write:

I assume I'm going to have to repeat a process 36 times (10 for each numeric digit and 26 for each letter, ... )

which means you may need 36 values. How are these to be used. Maybe you really don't need a query, only a function. You have 4 experts interested here, lets get to a solution.
Don't give up on this forum, it may be the best one out there!

[NeoPa]

@OldBirdman
I certainly do ;)

Particularly how you look to get the precise question sorted out as a basis for continuing (not just in this thread).

[shahaba]

Sorry for not responding sooner, this issue was put on the backburner for a while for various reasons. So far the information has been useful even though most of what is said here is going over my head. Let me answer what I can and if you want any more information I'll be checking back regularly.

I think that getting the results as fields in a table might be the most useful.

What I meant by 36 times is refering to if there was such built in function I'd be using it once for each digit 0-9 and once for each letter.

To be clear, I need the sum of the characters in all records of a table not the sum of each character in for each individual records

This in not a 1 time study. Currently the actual table I plan to run this on has 25000 records of varying length up to 14 characters. But I'll likely need to run this process everytime we get new data to append to this table.

[NeoPa]

Is the data for each record contained in a single field?

PS. With this new information we'd certainly be looking at doing it differently. I'll give it some thought.

[OldBirdman]

Does it matter as strAll = Str1 [& Str2 [& Str3 ...]]?
A function is going to be called 350,000 times. Isn't the question whether to do this in queries or code?
We're calling f(strAll, strLetter) for each letter 0-9, A-Z for each record.
Solution 1 - Queries - 1) SELECT f(strAll, "A") AS CountA, ..., f(strAll, "9") AS Count9 will result in 25,000 rows, 36 fields. 2)SUM values in each field
Solution 2 - Code (logic) - Do Until EOF; Do strX = "A" to "Z", "0" to "9"; txtLetter&strX = txtLetter&strX + f(strAll, strX); where txtLetter&strX are textboxes on a form.
Function f returns Len(strAll)-Len((Replace(strAll,strX, "")) per ADezii and this code could replace actual call either in SQL Statement or Code.
Disclaimer - The above is not intended to be code in any language. It is intended to point this discussion in one of two directions.
shahaba - My inquisitive mind would like to know the reason behind this need. Can you tell us. OldBirdman

[NeoPa]

Firstly, I'm going to proceed here, but please remember to respond to OB's post too.

In my considered opinion, the very real benefit of using Replace() for a single character, is more than lost when it has to be run on all the characters in the alphabet + numerals. This may not be true for an enormous string, but if we have many records then just putting the data together into a string would be enormously processor intensive, not to say time consuming.

I'm thinking on the basis that if the data in a record is up to 14 characters long, it's almost certainly in a single field.

I can't think of a reliable and efficient way of arranging the data such that it can be worked on as a single piece of data (string) so I've concluded the best way to handle this is to use DSum() to sum the values produced (by a function call we'll get to later) for each record in the table.

The function must essentially return the count of qualifying characters in the string data passed.

[NeoPa]

The following function should do the trick :

Public Function CountValid(strData As String) As Long
  Dim intX As Integer
 
  CountValid = 0
  For intX = 1 To Len(strData)
    If InStr(1, _
             "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 
             Mid(strData, intX, 1), _
             vbTextCompare) > 0 Then CountValid = CountValid + 1
  Next intX
End Function

Use vbBinaryCompare in line #9 if only upper case characters are required.

To get the total simply use :

Call MsgBox("Valid chars in database = " & _
            Format(DSum("CountValid([YourFieldName])", _
                        "[YourTablename]"), "#,##0\."))

[NeoPa]

If the format of the data is known always to fit any type of template, then that may well effect how best it can be done (the only characters that are ever included in the data, but which should not be counted, come from a list shorter than 36 for instance).

Otherwise there is an open invitation to post ideas. We're looking for those which are more efficient in execution rather than less though, so please review some of the known issue laid out earlier before contributing.

[shahaba]

The purpose of all this is creating Identity Tags by piecing these tags from single characters which must be requested in advance.

The data for each record is contained within a single field

Other characters that may appear are \ / -

[Unused01]

The purpose of all this is creating Identity Tags by piecing these tags from single characters which must be requested in advance.

Maybe this is an idea that has been proposed but should be forgotten. Random numbers or ? might be another approach to solving the underlying problem, which is to create Identity Tags.
In my defense, I realized that the data was probably in 1 field, but if not, the 2 or 3 fields could be concatinated, thus this issue could be ignored. I never considered creating a string of many thousands of characters, but 14 characters max.
The function "Function CountValid" does not do what is requested. It counts the total number of characters that are one of the 36 allowed. It does not count the number of each letter (i.e. How many "K"s?). From post #1:

I have a list of texts and I need to find out the number of times each character shows up.

[NeoPa]

@shahaba
How does counting the total number of these 36 characters relate to this :S

[NeoPa]

@shahaba
If these are the only other characters that may appear in the data anywhere, then the code can execute more quickly as :

Public Function CountValid(strData As String) As Long
  CountValid = Len(Replace(Replace(Replace(strData, _
                                           "/", ""), _
                                   "\", ""), _
                           "-", ""))
End Function

[NeoPa]

Firstly, please PM me if there's anything wrong with your account. I will do what I can to resolve any problem.
@CannotUseSite
No comment. I'm lost as to how this fits in at all with what was asked for before :S
@CannotUseSite
No defense required OB. I was also considering proceeding on those lines (I mean concatenating records, not just fields), until I thought it through more thoroughly. My comments were certainly not directed at your idea.
@CannotUseSite
In response to your prompting, Shahaba replied with an explanation of what he was trying to do fundamentally :
@shahaba
It is this new understanding that I'm working to (I think). I am no longer trying to answer the original question as it seems that it was a completely wrong approach anyway (going by what Shahaba has since posted). My understanding of that is that Shahaba was intending to call the function for each possible character then sum the results to produce the total (The only item of interest as far as I now understand).

[Unused01]

Originally Posted by shahaba
To be clear, I need the sum of the characters in all records of a table not the sum of each character in for each individual records

Not clear! "...the sum of the individual characters in all records", or "...the sum of the valid characters in all records"?

[NeoPa]

@CannotUseSite
I took it as the latter, but please retry your OldBirdman account. It should be working now.

PS. Please use me in future if you have any such, or similar, difficulties again. They're actually pretty rare, and I'm happy to resolve them quickly if / when they do crop up.

[OldBirdman]

I needless to say consider the first statement to be correct, as shahaba was clarifying, not changing the conditions for his request.

I assume I'm going to have to repeat a process 36 times (10 for each numeric digit and 26 for each letter, ...

About an efficient function - I would imagine that Instr() is much more efficient than Mid(), and that the code for calling a function is no worse than for executing a For...Next Loop. Replace() is least efficient. Therefore:

Private Function fCountOneLetter(iStart As Integer, strToTest As String, strFindThis As String) As Integer
Dim i As Integer
Static iCount As Integer
    i = InStr(iStart, strToTest, strFindThis)
    If i <> 0 Then
        i = fCountOneLetter(i + 1, strToTest, strFindThis)
        iCount = iCount + 1
    Else
        iCount = 0
    End If
    fCountOneLetter = iCount
End Function

[ADezii]

1. I've created an Algorithm that will calculate the Total Number of Characters (A-Z, 0-9) in a Variable Length Field in a Table. Simply pass the Name of your Table and Field to the fCountCharacters() Function and it will do the rest. The code assumes that the Field containing the Characters is Not Null. This is purely from a code perspective, and is my interpretation of the what the OP is looking for given his last, modified request. Is there a more efficient Method of doing this? Probably, but this one came right off the Top-of-My-Head (no Comment please, NeoPa). The one drawback that is evident is that there could be a staggering number of Loop Iterations for large Recordsets since there are 3 nested levels.
2. Function Definition:
   

   Public Function fCountCharacters(strTableName As String, strFieldName As String)
   Dim strCompareString As String
   Dim MyDB As DAO.Database
   Dim rstTest As DAO.Recordset
   Dim intCharCounter As Integer
   Dim intCharCtField As Integer
   Dim intFieldChar As Integer
   Dim intCumTotals As Integer
    
   strCompareString = "ABCDEFGHIJKLMNOPQUSTUVWXYZ0123456789"
    
   If DCount("*", strTableName) = 0 Then
     MsgBox "No Records exist in " & strTableName, vbCritical, "No Records"
       Exit Function
   End If
    
   Set MyDB = CurrentDb()
   Set rstTest = MyDB.OpenRecordset(strTableName, dbOpenSnapshot)
    
   DoCmd.Hourglass True
    
   With rstTest
     For intCharCounter = 1 To Len(strCompareString)       'A-Z and 0-9
       Do While Not .EOF       'Loop through All Records in the Recordset
         intCharCtField = Len(.Fields(strFieldName))       'Number of characters in the Field
           For intFieldChar = 1 To intCharCtField      'Iterate each character
             'Loop through all Characters in the Field for Current Record
             If UCase$(Mid$(.Fields(strFieldName), intFieldChar, 1)) = Mid$(strCompareString, intCharCounter, 1) Then
               intCumTotals = intCumTotals + 1     'Increment Counter for specific Counter
             End If
           Next
           .MoveNext
       Loop
         If intCumTotals > 0 Then
           Debug.Print "Number of " & Mid$(strCompareString, intCharCounter, 1) & "'s: " & _
                        Format$(intCumTotals, "00000")
         End If
         intCumTotals = 0      'Reset
         .MoveFirst    'Advance to 1st Record in ecordset
     Next
   End With
    
   DoCmd.Hourglass False
    
   rstTest.Close
   Set rstTest = Nothing
   End Function

3. Sample Function Call with required Arguments:
   

   Call fCountCharacters("Table1", "Field1")

4. Sample Data used for analysis:
   

   Field1
   ABC-432-9923
   CAFG-826
   JJ-J-1924K
   BBZ-00-11-888-PP
   7 7 7 7 7 7 7
   defrtyu-99-102-l

5. OUTCOME:
   

   Number of A's: 00002
   Number of B's: 00003
   Number of C's: 00002
   Number of D's: 00001
   Number of E's: 00001
   Number of F's: 00002
   Number of G's: 00001
   Number of J's: 00003
   Number of K's: 00001
   Number of L's: 00001
   Number of P's: 00002
   Number of U's: 00001
   Number of T's: 00001
   Number of U's: 00001
   Number of Y's: 00001
   Number of Z's: 00001
   Number of 0's: 00003
   Number of 1's: 00004
   Number of 2's: 00005
   Number of 3's: 00002
   Number of 4's: 00002
   Number of 6's: 00001
   Number of 7's: 00007
   Number of 8's: 00004
   Number of 9's: 00005

6. I'm done for now, download the Attachment is anyone is actually interested.

[ADezii]

@OldBirdman

This in not a 1 time study. Currently the actual table I plan to run this on has 25000 records of varying length up to 14 characters. But I'll likely need to run this process everytime we get new data to append to this table.

Just for curiosity, do you think that a Recursive Procedure will work under these Parameters?

[OldBirdman]

Wouldn't it be better to reverse these two statements?

For intCharCounter = 1 To Len(strCompareString)       'A-Z and 0-9 
    Do While Not .EOF       'Loop through All Records in the Recordset 

Then the file(table) would not be read through 36 times, which has got to be slower than reading it once. Seems from other things I've done that Access will requery or reread for about any excuse. Of course, you'd need an array to hold the 36 cumulating totals until the end of the function.
Why call UCase() when:

       If UCase$(Mid$(.Fields(strFieldName), intFieldChar, 1)) = Mid$(strCompareString, intCharCounter, 1) Then 
is the same as
       If StrComp(Mid$(.Fields(strFieldName), intFieldChar, 1)) ,Mid$(strCompareString, intCharCounter, 1), vbTextCompare) = 0 Then
is the same, if Option Compare Database, as
      If Mid$(.Fields(strFieldName), intFieldChar, 1) = Mid$(strCompareString, intCharCounter, 1) Then

The function StrComp() may not actually be a function call, but only the way the code is handled when executed.

[ADezii]

@OldBirdman

Wouldn't it be better to reverse these two statements?

Excellent point, OldBirdman. Your reversal should be more efficient and subsequently much faster.

Why call UCase() when:

To be honest with you, I cannot see how StrComp() would not be a Function Call, and I figured the String Version of the UCase$() Function would be faster, but of course I have no way of proving this. Whenever I get a chance, I'll perform some Benchmark Tests to see what approach is more efficient. We can even make it a horror movie: Ucase$() vs StrComp(). Seriously, thanks for the input.

[OldBirdman]

Many compiles/intepreters have functions that aren't really calls. The code is executed as if it were a simple statement, not a function call. IsNull might be such a function. There may be others. I have no basis for assuming any of this for Access VBA however.
I've tried to time different methods for efficiency, but I cannot get meaningful results. Must be that something else starts in background and messes up the testing. If you have a test method, I'd like to see it.
By the way:

Just for curiosity, do you think that a Recursive Procedure will work under these Parameters?

was answered in post #35, but as you posted almost simultaneously with your post #36, I guess you missed it.

[Stewart Ross]

In the attempt I've made at this below I have taken a different approach, which is to do minimal processing at the counting end and do the A to Z selection when reporting the counts. I use an array which counts the frequency of all ASCII characters in the strings concerned. This does not limit the final counts to A-Z and 0-9 should it be necessary to change the range later.

On a recordset comprising 66,568 single-textfield records totalling 5,930,963 characters the count and report function took just over 4 seconds to run start-to-finish.

Public Function fCountCharsInRecords(strRSName As String) As Long
    'This function counts the number of characters contained in the fields
    'of the recordset whose name is supplied as the argument.
    '
    'Assumption: the recordset comprises fields which contain solely text
    'to be counted. The number of fields is not predetermined, but all should be
    'text based. Null values acceptable.
    '
    'Source of these should be a query on base data which extracts
    'the text fields and no others.
    '
    'Method: this function generates a frequency count for all the ASCII character
    'codes in the source text fields. Lower case characters are counted
    'separately from uppercase, although the final results reported are for the
    'combined cases (that is the count for, "A" is the sum of the counts for "A"
    'and "a". The result file is a tab-separated text file on C containing the
    'character counts. The listing function can be modified as necessary for other
    'purposes.
    '
    'Returns the total number of characters in the character strings counted.
    '
    Dim chrCounts(255) As Long
    Dim RS As DAO.Recordset
    Dim intFields As Integer
    Dim lngChrCount As Long
    Dim strSomeString As String
    Set RS = CurrentDb.OpenRecordset(strRSName)
    intFields = RS.Fields.Count
    Call fClearChrCounts(chrCounts())
    Do While Not RS.EOF
        For intFields = 0 To intFields - 1
            strSomeString = Nz(RS.Fields(intFields))
            Call fCountChars(strSomeString, chrCounts())
            lngChrCount = lngChrCount + Len(strSomeString)
        Next intFields
        RS.MoveNext
    Loop
    RS.Close
    Call fListLetterAndNumberCounts(chrCounts())
    fCountCharsInRecords = lngChrCount
End Function
 
Public Function fClearChrCounts(chrCounts() As Long)
    Dim intI As Integer
    For intI = 1 To UBound(chrCounts)
        chrCounts(intI) = 0
    Next intI
End Function
 
Public Function fCountChars(strSomeString, chrCounts() As Long)
    Dim intI As Integer, intAsciiCode As Integer
    If Not IsNull(strSomeString) Then
        For intI = 1 To Len(strSomeString)
            intAsciiCode = Asc(Mid(strSomeString, intI, 1))
            chrCounts(intAsciiCode) = chrCounts(intAsciiCode) + 1
        Next intI
    End If
End Function
 
Public Function fListLetterAndNumberCounts(chrCounts() As Long)
    'Outputs list of character counts for 'A' to 'Z' and
    ''0' to '9', regardless of lower or uppercase original characters
    'Output file is a tab-separated textfile on C:
    '
    Dim intAsciiCode As Integer
    Open "C:\chrcount.txt" For Output As #1
    Print #1, "Char" & Chr(9) & "Count"
    For intAsciiCode = Asc("A") To Asc("Z")
        Print #1, Chr(intAsciiCode) & Chr(9) & chrCounts(intAsciiCode) + chrCounts(intAsciiCode + 32)
    Next intAsciiCode
    For intAsciiCode = Asc("0") To Asc("9")
        Print #1, Chr(intAsciiCode) & Chr(9) & chrCounts(intAsciiCode)
    Next intAsciiCode
    Close #1
End Function

Results

Char Count
A   350321
B    87295
C   196770
D   211750
E   441251
F   147846
G    87188
H   111309
I   244076
J    41821
K    39836
L   164205
M   162828
N   256494
O   288119
P    67275
Q     8000
R   302632
S   230041
T   274570
U   117314
V    36758
W    52701
X    38013
Y    90558
Z     1316
0    19418
1    32999
2    20188
3    15608
4     9277
5    11932
6     6641
7     6484
8     6174
9     5676

[FishVal]

@shahaba
Hmm.. Have you considered using one of known hashing algorithm.

[ADezii]

@OldBirdman
I use the timeGetTime() API Function for testing purposes. It is illustrated below:
http://bytes.com/topic/access/insigh...ttime-vs-timer

[ADezii]

Although not as efficient as Stewart's approach, I decided to take the much longer but compact route where all code functionality exists within the context of a single Function. The following code processess 100,000 Records consisting of a Fixed-Width Field of 14 Characters (MAX specified by OP), against the Comparison String (A-Z, 0-9). The code can definately be made more efficient as pointed out previously by Old Birdman in POst #38. The Function, a Sample Call, and subsequent Output are listed below:

Public Function fCountCharacters(strTableName As String, strFieldName As String)
Dim strCompareString As String
Dim MyDB As DAO.Database
Dim rstTest As DAO.Recordset
Dim intCharCounter As Integer
Dim intCharCtField As Integer
Dim intFieldChar As Integer
Dim intCumTotals As Long
 
strCompareString = "ABCDEFGHIJKLMNOPQUSTUVWXYZ0123456789"
 
If DCount("*", strTableName) = 0 Then
  MsgBox "No Records exist in " & strTableName, vbCritical, "No Records"
    Exit Function
End If
 
Set MyDB = CurrentDb()
Set rstTest = MyDB.OpenRecordset(strTableName, dbOpenSnapshot)
 
DoCmd.Hourglass True
 
With rstTest
  For intCharCounter = 1 To Len(strCompareString)       'A-Z and 0-9
    Do While Not .EOF       'Loop through All Records in the Recordset
      intCharCtField = Len(.Fields(strFieldName))       'Number of characters in the Field
        For intFieldChar = 1 To intCharCtField          'Iterate each character
          'Loop through all Characters in the Field for Current Record
          If UCase$(Mid$(.Fields(strFieldName), intFieldChar, 1)) = Mid$(strCompareString, intCharCounter, 1) Then
            intCumTotals = intCumTotals + 1     'Increment Counter for specific Counter
          End If
        Next
        .MoveNext
    Loop
      If intCumTotals > 0 Then
        Debug.Print "Number of " & Mid$(strCompareString, intCharCounter, 1) & "'s: " & _
                     Format$(intCumTotals, "###,###,###")
      End If
      intCumTotals = 0      'Reset
      .MoveFirst    'Advance to 1st Record in ecordset
  Next
End With
 
DoCmd.Hourglass False
 
rstTest.Close
Set rstTest = Nothing

Call fCountCharacters("Table2", "Field1")

OUTPUT:

Number of A's: 99,980
Number of C's: 117,427
Number of D's: 33
Number of E's: 16,988
Number of M's: 33
Number of N's: 2
Number of O's: 99,967
Number of P's: 200,317
Number of S's: 47,860
Number of T's: 17,294
Number of 0's: 241,183
Number of 1's: 39,604
Number of 2's: 45,270
Number of 3's: 177,512
Number of 4's: 79,460
Number of 5's: 75,140
Number of 6's: 41,465
Number of 7's: 36,615
Number of 8's: 32,285
Number of 9's: 31,466

[shahaba]

Problem solved. Next time I need help, I'll try to be a little more descriptive so there isn't any confusion. Thanks all,