Search The Web

Today's Headlines

Friday, November 12, 2010

Microsoft Access Tips & Tricks: Count Letters, Words, Sentences and Paragraphs

Counting the number of letters, words, sentences and paragraphs can come in handy for calculating statistics, not just involving the counts, but also derived statistics that can give you readability scores and other details about a given piece of writing.

If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries, crosstab queries, working with system objects, listing table fields, finding unmatched rows, calculating statistics with grouping, job-candidate matching, job-candidate matching with skill levels, great circle distances, great circle initial headings, using Excel functions in Access, using the windows file-picker, using the Access date-picker, setting tab indexes quickly and correctly, pre-filling forms based on previous entries, highlighting form controls, conditional formatting, and performing date manipulations in Access.

The code below illustrates how to use the built-in Access functions Len(), Mid(), Chr(), and Trim() to accomplish the counting. It is important to understand how these functions work so that you can take advantage of their power in other applications also. There are a few things to note about the code:
  • A carriage return in Microsoft Access is represented by Chr(13) + Chr(10) (i.e. the occurrence of ASCII code 13 followed immediately by ASCII code 10). Thus, this is used to find ends of paragraphs. Note that it is not Chr(10) + Chr(13).
  • Adjacent carriage returns are counted as just one.
  • Only the numbers 0 through 9, and uppercase and lowercase letters of the English alphabet are considered letters. Spaces, punctuation marks, accented characters, etc., are not included in the count of letters. If you want to include them in your count, you can easily find their ASCII code and enhance the letter-counting code below to include those characters also.
  • The code will not count sentences correctly when the text contains acronyms that include periods (such as B.S. in Engineering, for instance). That is because the occurrence of ".", "!" and "?" signifies the end of one sentence and the beginning of the next. If you want to include other punctuation marks to mark the ends of sentences (such as ";"), you can modify the code easily to handle that.
  • Also, pay careful attention to the comments in the code below. For instance, we assume paragraphs are not indented, and that the last paragraph does not have a carriage return after it. If these conditions are not true, it should be easy enough to modify the code to take the actual conditions into account. But you can not do this unless you are familiar with the assumptions used in the code as presented below.
Without further ado then, here are the functions to return the number of paragraphs, sentences, words and letters in a passage of text passed to them:
Option Compare Database
Option Explicit

'This code is explicitly written to be compatible with all versions of Access starting with
'Access 97. That is why the code does not include functions such as split() or replace().

Public Function numparagraph(note As String) As Integer

'This function counts the number of occurrences of carriage returns and returns that as the
'number of paragraphs. We assume that the last paragraph does not have a carriage return
'following it. If that is not true, remove the line that accounts for the last paragraph
'from the code below.

Dim SearchedTill As Integer
numparagraph = 0
If Len(note) = 0 Then
Exit Function
Else
numparagraph = 1 'This will account for the last paragraph which is not followed by a carriage return
End If

For SearchedTill = 1 To Len(note)
If Mid(note, SearchedTill, 1) = Chr(13) Then
If Mid(note, SearchedTill + 2, 1) <> Chr(13) Then 'Adjacent carriage returns are counted as one.
numparagraph = numparagraph + 1
End If
End If
Next SearchedTill
End Function

Public Function numsentence(note As String) As Integer

'This function counts the number of sentences by counting all occurrences of ".", "!" and "?"
'This may not lead to an accurate count of the number of sentences, but will be close. If you
'tend to end sentences with other punctuation marks, you can include them in the list of
'punctuation marks counted.

Dim SearchedTill As Integer
numsentence = 0
If Len(note) = 0 Then
Exit Function
Else
numsentence = 1 'This will account for the last sentence which is not followed by a space or carriage return.
End If

For SearchedTill = 1 To Len(note)
If Mid(note, SearchedTill, 2) = ". " Or Mid(note, SearchedTill, 2) = "! " Or Mid(note, SearchedTill, 2) = "? " Or _
Mid(note, SearchedTill, 2) = "." & Chr(13) Or Mid(note, SearchedTill, 2) = "!" & Chr(13) Or Mid(note, SearchedTill, 2) = "?" & Chr(13) Then
numsentence = numsentence + 1
End If
Next SearchedTill
End Function

Public Function numword(note As String) As Integer

'This function counts the number of words by counting spaces. Adjacent spaces are considered one space.
'Each paragraph break also contributes to the number of words if there are no spaces in front of
'words that start paragraphs (assuming paragraphs are not indented).

Dim SearchedTill As Integer

note = Trim(note)
numword = 0
If Len(note) = 0 Then
Exit Function
Else
numword = 1 'This will account for the last word which is not followed by a space or carriage return.
End If

SearchedTill = 1
For SearchedTill = 1 To Len(note)
If Mid(note, SearchedTill, 1) = " " Or Mid(note, SearchedTill, 1) = Chr(10) Then
If Mid(note, SearchedTill + 1, 1) <> " " And Mid(note, SearchedTill + 2, 1) <> Chr(10) Then
'Adjacent spaces and carriage returns are counted as one
'Note that I use chr(10)'s to count carriage returns because it is easier to spot
'a space followed by a single letter followed by a carriage return if we check for
'chr(10) as opposed to chr(13) (the second if condition would miss the former occurence,
'however unlikely it is to actually occur.
numword = numword + 1
End If
End If
Next SearchedTill

End Function

Public Function numletter(note As String) As Integer

'This function counts the number of letters in a string, excluding spaces,
'punctuation marks, quotes, parentheses etc. Only numbers and letters are counted.
'If you just want the total length of a string, use the len() function instead of this.
'If you want to include other characters in the count, you have include their ascii codes
'in the case statement below or explicitly check each character in the string against your
'list of characters.

Dim SearchedTill As Integer
Dim AscValue As Integer

note = Trim(note)
numletter = 0
If Len(note) = 0 Then
Exit Function
End If

SearchedTill = 1
For SearchedTill = 1 To Len(note)
AscValue = Asc(Mid(note, SearchedTill, 1))
Select Case AscValue
Case 48 To 57, 65 To 90, 97 To 122 'These ascii codes represent the numbers 0-9, letters A-Z and letters a-z
numletter = numletter + 1
End Select
Next SearchedTill

End Function
Hope this post has been helpful in solving any problems you might have had with counting letters, words, sentences and paragraphs in Access. The VBA code in this post has been tested in Access 2003 and should work without any problems in all versions of Access from Access 97 on up. If you have any problems or concerns, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future posts, please feel free to let me know through your comments too. Good luck!

No comments:

Visitors Country Map

Free counters!

Content From TheFreeDictionary.com

In the News

Article of the Day

This Day in History

Today's Birthday

Quote of the Day

Word of the Day

Match Up
Match each word in the left column with its synonym on the right. When finished, click Answer to see the results. Good luck!

 

Hangman

Spelling Bee
difficulty level:
score: -
please wait...
 
spell the word:

Search The Web