Newbie: Counting the number of words in a Memo field

  • Thread starter Thread starter CF
  • Start date Start date
C

CF

Is it possible to create a query using a built-in function to count the
number of words in a Memo type field in a table with approximately 5000
records? Any help would be appreciated. Thanks in advance...Carlos
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Here is a function that I've used:

Function WordCount(strWords As String) As Long
' Purpose:
' Classic wc algorithm
' Created:
' mgf 5/4/2004 translated from Software Tools In Pascal, p. 17.
' Modified:
'

Const BLANK = " " ' Space

Dim nw As Long ' Number of words
Dim c As String ' Current character being examined
Dim inword As Boolean ' Is c inside a word?
Dim i As Long ' Character index

' initialize
nw = 0
inword = False
i = 1

Do While i <= Len(strWords)
c = Mid$(strWords, i, 1)
If c = BLANK Or c = vbCr Or c = vbLf Or c = vbTab Then
inword = False
ElseIf Not inword Then
inword = True
nw = nw + 1
End If
i = i + 1
Loop

WordCount = nw

End Function

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJfsIoechKqOuFEgEQIlfwCdF2doGtXF1f7qdiZhmzFTsJEDOhkAoKfp
7jB3hTxN1zo1z/yr4uQkOE90
=Hrar
-----END PGP SIGNATURE-----
 
Hi Carlos,
PMFBI
Just remember not to sort on the column
where you calculate WordCount or the intensive
function will be run twice.
Apologies again,
Gary Walter
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you do want to sort on the WordCount() column you can use the ordinal
number of the column in the SELECT clause in the ORDER BY clause instead
of the column's expression. E.g.:

SELECT Col1, Col2, WordCount(Col3) As WordCount
FROM TableName
ORDER BY 3 -- instead of ORDER BY WordCount(Col3)

This will sort the 3rd column in the SELECT clause by its value rather
than evaluating the expression again.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJkdIoechKqOuFEgEQJ9UwCfbbxK6jHTACWcS9p+J7/mOJVW4c4AnAt/
HrvAZ3gzkXdTPcCqH9tIh2/T
=oROK
-----END PGP SIGNATURE-----
 
If you sort on a memo field you "automatically" truncate its contents
to 255 characters. If you really have to sort on a memo field, but you
also want to display its contents, you have to pull it in twice and
display the second copy (the column that you are not sorting on).


Hi Carlos,
PMFBI
Just remember not to sort on the column
where you calculate WordCount or the intensive
function will be run twice.
Apologies again,
Gary Walter
Click to expand...

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Hi MG,

I'm sorry I brought it up.

I had a function

Public Function fWordCount(pMemo As Variant) As Long
On Error GoTo Err_fWordCount
Dim arrWords As Variant, i As Long, lngCount As Long

If Len(Trim(pMemo & "")) = 0 Then
fWordCount = 0
Exit Function
End If

lngCount = 0

pMemo = Replace(pMemo, vbCr, " ", 1, -1, vbTextCompare)
pMemo = Replace(pMemo, vbLf, " ", 1, -1, vbTextCompare)
pMemo = Replace(pMemo, vbTab, " ", 1, -1, vbTextCompare)

arrWords = Split(pMemo, " ", -1, vbTextCompare)

For i = 0 To UBound(arrWords)
If Len(arrWords(i) & vbNullString) > 0 Then
lngCount = lngCount + 1
End If
Debug.Print i & ": " & arrWords(i) & ";" & lngCount
Next i
fWordCount = lngCount

Exit_fWordCount:
Exit Function

Err_fWordCount:
MsgBox Err.Description
Resume Exit_fWordCount
End Function

and ran it against a "this-and-that" db
that had 2829 records with a memo
field "FullLine" that could be quite large.

SELECT tbl_MailDBX.ID, tbl_MailDBX.Topic,
fWordCount([FullLine]) AS WordCount
FROM tbl_MailDBX
WHERE (((tbl_MailDBX.ID)=1))
ORDER BY tbl_MailDBX.Topic;

This is only working on one record
and when I run it, the Debug.Print
gives me one cycle.

If I try to sort on the calc field

SELECT tbl_MailDBX.ID, tbl_MailDBX.Topic,
fWordCount([FullLine]) AS WordCount
FROM tbl_MailDBX
WHERE (((tbl_MailDBX.ID)=1))
ORDER BY fWordCount([FullLine]);

the Debug.Print shows 2 "cycles."

Sorting like

SELECT tbl_MailDBX.ID, tbl_MailDBX.Topic,
fWordCount([FullLine]) AS WordCount
FROM tbl_MailDBX
WHERE (((tbl_MailDBX.ID)=1))
ORDER BY 3;

I still get 2 "cycles."

My point was that if I put a single quote
in front of Debug.Print and run the query
on all the 2829 records

SELECT tbl_MailDBX.ID, tbl_MailDBX.Topic,
fWordCount([FullLine]) AS WordCount
FROM tbl_MailDBX
ORDER BY tbl_MailDBX.Topic;

it opens immediately.

If I sort by calc field

SELECT tbl_MailDBX.ID, tbl_MailDBX.Topic,
fWordCount([FullLine]) AS WordCount
FROM tbl_MailDBX
ORDER BY fWordCount([FullLine]);

I did not have the patience to wait for
it to finish.

Likewise for

SELECT tbl_MailDBX.ID, tbl_MailDBX.Topic,
fWordCount([FullLine]) AS WordCount
FROM tbl_MailDBX
ORDER BY 3;

So.....I thought a "newbie" might want
to know that.

Peter, I don't recall saying anything
about sorting on the memo field,
but a "newbie" might want to know that
also.


Gary Walter
 
Back
Top