RecordSets

[Hunter666]

i need to capture the data from a query in a record set and then count the number of records in the set......can anyone help me??

 

[chrismcbride]

If you assign the results of a select query to a Recordset variable, you can use the .RecordCount property to give you the number of rows. You need to use the .MoveLast method first in order to populate the recordset.
HTH
Chris

 

[Hunter666]

Thank you. Do you have an example?
Paul

 

[cogent1]

Private Sub GetCount
Dim db as database
dim rs as recordset
Dim MyRecordCount as Integer

set db=currentDB
set rs =db.Openrecordset("MyQuery")
with rs
.moveFirst
.Movelast
MyRecordCount=.RecordCount
If MyRecordCount = 0 then
Msgbox "No Record"
Else Msgbox "There are " & MyRecordCount & " records"
End If
End with
End Sub


[This message has been edited by cogent1 (edited 05-29-2002).]

 

[Hunter666]

Many thanks to you both for your help!
Paul

 

[Hunter666]

Im still having a little trouble with this.
The code above dosnt seem to work for me. it complains that "Dim db as Database" is not a valid variable. Can anyone help?
Paul

 

[David R]

Make sure the reference to DAO 3.6 is checked in Tools>References. Access2000 and later use ADO by default, but the example uses the older DAO methods.

 

[cogent1]

Yes, this is true. The syntax for the two systems is different, with ADO now being the default. For the small databases I write, DAO is my preference and that of many other users even though it's now becoming obsolescent. This example will work if you load the DAO 3.6 library as recommended. But perhaps you can achieve your aims without writing any code. What are you going to do with the record count once you've got it?

 

[Hunter666]

I have counted the number of records without using much code. However i want to create a more extensive report on the database. i.e. there are X amount of companies with X amount of contact and X amount of email addresses and so on.... I have now added the reference and its no recognising the variable. However im getting a Type mismatch on the line "set rs = db.openrecordset("EmailCount")" Any Ideas?

 

[cogent1]

Hunter
Is "emailcount" a saved query? ie, do you have a query of that name visible in the database window and is it a select query?

What is the name of the table containing the data you want to count and what criteria are you applying to filter the records?

 

[Hunter666]

im trying to count the email addresses in this example. the table is called "Contacts" the field storing the email addresses is called "Email". The Select query im using to select only Email >"" is called "EmailCount".

The way ive been doing it before this is to open a form with "EmailCount" as the controlsource. All it does is keeps going to the next record and each time adds one to a string then on error (when it reaches the last record" it displays the string in a text box. Very Slow way of doing things.

 

[cogent1]

If all you want to do is return a single row with the count of a particular field, you can use an aggregate query, ie, one with the Totals row enabled and with COUNT as the aggregate criterion.

If this doesn't suit your requirements, please give the following information:

Which data type is your email field (number, text, yes/no)?

What is the default setting for the field - Null(default), empty string (""), or other?

Does the Query return the correct number of rows when run from the database window?



[This message has been edited by cogent1 (edited 05-30-2002).]

 

[Hunter666]

The Email field is Text
Default is Null
and yes the query normally works fine.

Paul

 

[cogent1]

in that case, I don't see why the code won't work. It's a straightforward process. "Type mismatch" means that an unexpected data type has been encountered, e.g. your query criterion is a string when it should be an integer.Send the database to my email address with sample data and I'll debug it
Colin