Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 21st, 2005, 03:06 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old March 22nd, 2005, 09:52 AM
Authorized User
 
Join Date: Feb 2005
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
 
Old March 22nd, 2005, 10:23 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old March 22nd, 2005, 10:24 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Combining the average of multiple rows into one Saidar SQL Server 2005 2 December 24th, 2007 03:45 AM
Getting an average using SQL vs VBScript mat41 Classic ASP Professional 1 November 1st, 2007 06:33 PM
How to average a series of integers using assembly smithyluke Assembly Language 0 October 9th, 2007 12:42 AM
7Day Moving Average Gezza SQL Language 0 November 21st, 2003 02:42 AM
find the average value from a column of values codespike Classic ASP Basics 2 August 15th, 2003 07:37 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.