Word Count in Cell? 1

[CJSwed]

Hi.. I have a very large database that contains products and is about 5,000 rows long. I am trying to find a way to get a word count on all the cells in a specific column.


So if the table looks like this:

1 A B C D E F
2
3
4
5
6
7
8

I need a query to go down column F and create a column G and put in the word count for the number of words in Cell F2, F3, etc. What would be even better is if this could auto update as words are removed from the cells in column F.

I know Excel is probably a better way to do this but I need this to be on going (whether manually running a query or automatically updating) and Access is where is all this information is stored.

Any ideas?

Thanks guys..

Chris

 

[dhookom]

There are no "cells" in Access. I assume you want to display a calculated column that contains the number of words in a field. One simple method is to use something like:

WordCount: Len([YourField])- Len(Replace([YourField]," ",""))+1

This assumes there is a single space between each word which is the norm.

Duane
Hook'D on Access
MS Access MVP

 

[CJSwed]

Thanks for the response, I am having a problem with it though. I get an error message of Invalid SQL statement; 'Delete', 'Insert', 'Procedure', 'Select', or 'Update'

Lets say the name of the table is trial and the column that I need the count on is called contents and the number that I wish to put the count into is called contentsno .. would the correct format be:

WordCount: Trial([contents])- Trial(Replace([Contentsno]," ",""))+1

 

[dhookom]

Don't use the name of the table as a function name. Use the Len() function as I suggested. Why do you have two different field names?

WordCount: Len([Contentsno])-Len(Replace([Contentsno], " ",""))+1

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

Duane,

I think CJSwed wants to put the number of words in the Contents field into the ContentsNo field.

ContentsNo: Len([Contents]) - Len(Replace([Contents], " ", "")) + 1

Randy

 

[CJSwed]

Randy,
You are absolutely right. Duane and Randy, I appreciate your help very much. I actually accomplished this, this morning and didn't get a chance to get back in here to say thanks. I am constantly amazed at how much you guys know and how powerful SQL is. I have my books on SQL and dable in it as needed but am nowhere near as knowledgable as you guys. A big big thanks to both of you for all of your help, as usual, this just saved me countless hours of manual work (and that is what SQL is all about, right? saving time).

A big big thanks, I appreciate the help guys!!

Chris

 

[LarrySteele]

Duane - nice method. I was thinking along the same lines - counting number of spaces +1, but I like the way you accomplished the counting.

 

[dhookom]

Larry,
Thanks for the compliment but I think I got the solution from some posting in a public forum long ago. The only credit I should get is remembering ;-)

Duane
Hook'D on Access
MS Access MVP

 

[lespaul]

remembering is the important part! Especially when remembering gets to be challenging!

Leslie

Come join me at New Mexico Linux Fest!