Count records in the recordset?

polina · Jan 21, 2003

hi,

how to count the number of records returned in the recordset?

thanks

Mile-O · Jan 21, 2003

How would you like to do this? In Code, in a query, or on a form?

polina · Jan 21, 2003

i've got

sql="Select Name, Transit from Table1 where Transit=" & varTransit

Set rst = CurrentDb.OpenRecordset(sql1, dbOpenDynaset)

Then I want to know how many records have been returned and assign that number to the variable.

Mile-O · Jan 21, 2003

First things first;

On your SQL statement, add a semi-colon at the end.

sql="Select Name, Transit from Table1 where Transit=" & varTransit & ";"

To find out how many records are in a recordset you can use the .RecordCount property of a RecordSet

i.e

variablename = rst.RecordCount

Just noticed that in the code you posted you have a string called sql and in the next line you are opening a recordset called sql1 - I'm guessing it's a typo.

polina · Jan 21, 2003

Thanks

Can you ask one more question....

Can I manipulate the recordset as it were a table

Say, can I use such sql statement: Select * from rst where rst!Name <> "Paul"

Please advise

Thanks

polina · Jan 21, 2003

so, you're saying that the following will work

sql1 = "select * from rst where rst!Name <> "Paul"
Set rst1 = CurrentDb.OpenRecordset(sql1, dbOpenDynaset)

WayneRyan · Jan 21, 2003

polina,

sql1 = "select * from rst where rst!Name <> "Paul"
Set rst1 = CurrentDb.OpenRecordset(sql1, dbOpenDynaset)

Should be:

sql1 = "select * from YourTable " & _
"where Name <> 'Paul'"
Set rst1 = CurrentDb.OpenRecordset(sql1, dbOpenDynaset)

btw,
Field names should not be Name, Date or other words that
might be reserved.

Wayne

Count records in the recordset?

polina

Registered User.

Mile-O

Back once again...

polina

Registered User.

Mile-O

Back once again...

polina

Registered User.

polina

Registered User.

WayneRyan

AWF VIP

Similar threads

Users who are viewing this thread