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)
44 31197 @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
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)>
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.
@missinglinq
Nice job Linq! Your code is definitely more compact, efficient, practical, portable, digestible, etc., etc., etc.
@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?
@ADezii - This is inefficient because not all characters are expected to be in a string being processed.
- This is completely unacceptable for unicode strings.
@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?
It certainly could, but there is no reason to search a string for all unicode characters.
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)> @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.
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
Sorry guys, but I still feel that Linq's approach, or a variation thereof, is the way to go on this one!
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)>
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;
-
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.
:D
Aim? Who does care. Just fire.
NeoPa 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB @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).
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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 32,556
Expert Mod 16PB
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 32,556
Expert Mod 16PB
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.
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 \ / -
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 32,556
Expert Mod 16PB @shahaba
How does counting the total number of these 36 characters relate to this :S
NeoPa 32,556
Expert Mod 16PB @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 32,556
Expert Mod 16PB
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).
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 32,556
Expert Mod 16PB @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.
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
- 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.
- 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
- Sample Function Call with required Arguments:
- Call fCountCharacters("Table1", "Field1")
- 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
- 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
- I'm done for now, download the Attachment is anyone is actually interested.
@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?
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.
@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.
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.
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
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
Problem solved. Next time I need help, I'll try to be a little more descriptive so there isn't any confusion. Thanks all,
Sign in to post your reply or Sign up for a free account.
Similar topics
by: dan |
last post by:
this is a program to count average letters per word. i am able to
count the total number of letters, but not words. How do you count the
total number of words in a text file, so i am able to divide...
|
by: Jerry |
last post by:
Hi -
Using XSL I need to count the number of times character appears in a
string. My guess is I need to recurse through the value of x, but I'm
having trouble getting my head around the...
|
by: Martin Nadoll |
last post by:
Hello,
I am working on a form-validation script.
There is a input-field where you input a float or integer numper (maximum
price to output in database-query).
But my Cold-Fusion Query...
|
by: Cro |
last post by:
Hello Access Developers,
I'd like to know if it is possible to perform a count in an expression
that defines a control source.
My report is based on a query. In my report, I want a text box to...
|
by: Samuel R. Neff |
last post by:
What options are available for doing full-text searches of database
data without using a database-specific full-text engine?
The only option I've found is Google's Search Appliance but it's an...
|
by: Paul Mars |
last post by:
I need to limit multiline textbox to 3 lines of text and if there are less
then 3 lines when leaving, add empty line holders. How can I do this??
Thanks,
paul
|
by: beanie |
last post by:
i am a beginer in c programming and i am trying to Create a Concordance of Word Count for a Text File but my code is not working.pls can anyone helpme out.here is my code:
#include <stdio.h>...
|
by: arulk |
last post by:
Can we count wraping Character in xsl? character reading from xml....
anyone know this can send me sample code ...
|
by: darktemp |
last post by:
I have a line of characters that need to be separated. One example is like this:
513413;dialog_513413;Sally Mae has some jobs for you.;
Three sets of data all split into three groups placed in...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |