Total words in a field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

Is it possibe to set a query, that will return the total number of words in
a particular field, within the whole table? The field in question is a memo
field. I ideally want to return the number as label text, so I can display it
on a form, as part of a summary.

Thanks in advance,

Darren
 
Darren-

First, you need to write a bit of code to "count words" in a string. You
can copy the code posted below my sig to do the job -- put it in a Standard
module - one you can see in the list of modules in the database window.
Then, you can use the function in any query:

SELECT MyTable.ID, MyTable.MemoField, CountWords(MyTable.MemoField) As
NoOfWords
FROM MyTable

--
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

======================
Public Function CountWords(varToTest As Variant) As Integer
'-----------------------------------------------------------
' Input: A string
' Outputs: Integer count of the number words
' in the string.
' Created By: JLV 01/31/95
' Last Revised: JLV 01/31/95
'-----------------------------------------------------------
Dim intI As Integer, strWork As String, intCount As Integer
CountWords = 0
If VarType(varToTest) <> vbString Then Exit Function
strWork = Trim(varToTest)
If Len(strWork) > 0 Then
' Start at 1 if string is not empty
' -- adjusts for no blank at the end
intCount = 1
Else
intCount = 0
End If
Do
' Find the next blank (end of a word)
intI = InStr(strWork, " ")
If intI = 0 Then Exit Do
intCount = intCount + 1
' Reduce the string to the next non-blank
strWork = Trim(Mid(strWork, intI + 1))
Loop
CountWords = intCount
End Function
==========================
 
Thanks John,

Just one more question. I have managed to use the code below, and build a
query that gives me a single figure for the table, which is just what I want.
How do I get that figure to show as label text on a form though, or if this
is not possible, in a text box? The form is already bound to another
different table. I basicly have an "information" section on the form, and
wanted to illustrate the size of certain fields, on certain tables.

Thanks for any help,

Darren
 
One way to do it would be to base that form on the query that includes that
calculated field that returns the number of words in your memo field.
You're already basing the form on the table that underlies the query you
just built, so if you add the other fields from the table that you use in
the form, you can base the form on the query instead of the table. Except
you would then be able to add calculated fields (like # words in memo field)
to any form or report based on the query. And since you can add criteria
and sort fields in queries, you have even more control over your forms and
reports. That's why it's a good idea to base your forms and reports on
queries rather than tables.

You would probably need to add additional fields to that query from the
underlying table to get all the fields you need into the form. Once you've
add all the fields you need to the query, just replace the Record Source
property of the form with that query, and you'll be in a position to add
that calculated field to your form.

The easiest way to do it would be to drag that field into the form as a text
box from the field list. You can easily make the text box look like a label
by setting it's Back Style and Border Color to Transparent, and adjusting
other format properties as you like. You might also want to delete the
label for the text box itself, since you're wanting the contents to look
like a label.

HTH

Paul
 
Darren-

As Paul suggested, you can include the expression in the Record Source of
your form and simply add a bound text box that looks like a label to the
field. You can also add a text box and use theDLookup or DSum functions as
the Control Source to dynamically look up the value independent of the
record source. I'd have to know about the Record Source of the form and the
query you're using to calculate the word total to give you more specific
advice.

--
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
BTW, I've gotten a lot out of your books over the years, including my
introduction to Access and db development, so thanks for writing them, John.

Paul
 
Public Function CountWords(varToTest As Variant) As Integer
'-----------------------------------------------------------
' Input: A string
' Outputs: Integer count of the number words
' in the string.
' Created By: JLV 01/31/95
' Last Revised: JLV 01/31/95
'-----------------------------------------------------------
Dim intI As Integer, strWork As String, intCount As Integer
CountWords = 0
If VarType(varToTest) <> vbString Then Exit Function
strWork = Trim(varToTest)
If Len(strWork) > 0 Then
' Start at 1 if string is not empty
' -- adjusts for no blank at the end
intCount = 1
Else
intCount = 0
End If
Do
' Find the next blank (end of a word)
intI = InStr(strWork, " ")
If intI = 0 Then Exit Do
intCount = intCount + 1
' Reduce the string to the next non-blank
strWork = Trim(Mid(strWork, intI + 1))
Loop
CountWords = intCount
End Function
==========================
Click to expand...
Hi John,

I once saw what I thought was an ingeneous variation of
the CountWords routine (200x) that used Split function
and returned UBound +1.

Dim arrWords As Variant
If Len(strWork) > 0 Then
Click to expand...
arrWords = Split(strWords," ", -1,1)
CountWords = UBound(arrWords) +1
Else
CountWords = 0
End If
Click to expand...

This only worked if the text did not contain more than
one space between words....so in the end...

Public Function CountWordswithSplit(varToTest As Variant) As Integer
'-----------------------------------------------------------
' Input: A string
' Outputs: Integer count of the number words
' in the string.
'-----------------------------------------------------------
Dim intI As Integer, strWork As String, intCount As Integer, arrWords As Variant
CountWordswithSplit = 0
If VarType(varToTest) <> vbString Then Exit Function
strWork = Trim(varToTest)
If Len(strWork) > 0 Then
arrWords = Split(strWork, " ", -1, vbTextCompare)
intCount = 0
For intI = 0 To UBound(arrWords)
If Len(Trim(arrWords(intI))) > 0 Then intCount = intCount + 1
Next intI
CountWordswithSplit = intCount
Else
CountWordswithSplit = 0
End If
End Function

I'm not saying this is "better"

BTW, my nephew was taking a college course in Access
and having trouble. I took over 2 of your books (design and SQL),
spent a few hours going through overview, left the books for him to
continue reading overnight, and he aced his test the next day!

Thought you'd like to know.

Gary Walter
 
Gary-

If you look at the date on the code, I wrote it before Split was available
in Access. My code should work in just about any version of Access (maybe
even Version 2!). I like your Split version for Access 2000 and later.

Glad to hear your nephew was able to make good use of my books!

--
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Hi, John. I need to use this code but I don't know to much of coding in
access. Can you help me? how do you put the code in a query? I start a new
database with only the memo field.
 
Victor-

Select the module tab in the Database window and create a new module. Paste
the code I posted earlier for CountWords into the module. Select Compile
from the Debug menu - and if all goes well, save the module.

Now you can call the CountWords function from any query in the database just
like you can any built-in function such as Date or Left.

--
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Hi John... It's realy help.. thanks...

John Viescas said:
Victor-

Select the module tab in the Database window and create a new module. Paste
the code I posted earlier for CountWords into the module. Select Compile
from the Debug menu - and if all goes well, save the module.

Now you can call the CountWords function from any query in the database just
like you can any built-in function such as Date or Left.

--
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Click to expand...
 
Back
Top