You may need to make sure that you are using Microsoft DAO 3.6 Object Library. Hit <Alt><f11> and then got to Tools>Referencese and make sure this is selected. This should allow you to get this data. However, if you are going to a SQL database for this data, then you may need to use other connection and recordset objects.

Where is your data?


mmcdonal

I tried it after i added the reference you mentioned but still couldnt get it to work. I saced the functions in a module and then used the query :

SELECT DISTINCT Longest() AS Longest, Shortest() AS Shortest, Average() AS Average
FROM [Table];

I am getting an error saying type mismatch

there is one table in ms access db.. called 'Table' and the data that needs to be checked for the length of words are in a field called 'Message'

I dont understand this bit in the code :

stSQL = "SELECT * FROM [Table]"

stLongest = "I"

Set db = CurrentDb()
Set rs = db.OpenRecordset(stSQL)

will this be necessary to get it to connect to the db or even have the sql string here.. in the other example i had this wasnt needed.. as far as im aware..

do you have any advice?

Hi,

   Try moving the DAO reference up in the list one space at a time and see if this works. If the data type mismatch is the only error you are getting, then one of the fields is trying to capture the wrong type of data.

   This part of the code does the following:

stSQL = "SELECT * FROM [Table]" 'when you open the recordset, you need to set the rs with some sort of recordset. SELECT * FROM Table selects all records from the table Table. This data is then held in memory so that you can manipulate it with the code.

stLongest = "I" ' this sets the longest word as "I", or one character long. The next word parsed that is longer than I, such as ME, will replace this, and the length of the longest word will go to 2 characters, or the word me.

Set db = CurrentDb() ' sets the database as the current database. In other words, this is the database you want to connect to, not some other database. If the data is stored in another database, then we need to connect to that database.

Set rs = db.OpenRecordset(stSQL) 'this uses the db object to open the recordset that we are going to parse and populates the recordset with all the records from the table Table.

This works in an Access 2000 mdb file, that has the table Table within it, with a DAO reference listed as the second lowest.

The query returns the longest word (not it's character count), the shortest word, and the average characters per word.


mmcdonal

I would also advise getting Alison Balter's Mastering Access Desktop Development.

mmcdonal