jbauer22
asked on
DAO RecordCount
I'm trying to get a Count of the records "rstTemp". The problem seems to be when I use the Where clause in the SQL source. If I just specify the Table [Qry-Export Summary] as the source or Select * from [Qry-Export Summary] I am able to get a record count. When I insert the Where clause the record count returns 1 when I know there are more records.
Suggestions?
Sub Temp()
Dim x
Dim db As Database
Dim rstTemp As Recordset
'--- The following only returns a record count of 1?
Set rstTemp = db.OpenRecordset("Select * From [Qry-Export Summary] Where LAYOUT_ID = 'migl606r.sqr'")
x = rstTemp.RecordCount
End Sub
Suggestions?
Sub Temp()
Dim x
Dim db As Database
Dim rstTemp As Recordset
'--- The following only returns a record count of 1?
Set rstTemp = db.OpenRecordset("Select * From [Qry-Export Summary] Where LAYOUT_ID = 'migl606r.sqr'")
x = rstTemp.RecordCount
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
dovholuk, I concur totally. Count is more efficient, especially with an ungodly large recordset.
<listening..>
ASKER
My recordset would only have 3 rows a max. Thanks.
another alternative is to open two recordsets (or one right after the other). the first recordset simply returns the COUNT of the SELECT statment, the next recordset actually returns the SELECT statement.
so your code would look like:
Sub Temp()
Dim x
Dim db As Database
Dim rstTemp As Recordset
'this sets the variable x to the count
x = db.openrecordset("SELECT COUNT(*) FROM [Qry-Export Summary] WHERE Layout_ID = 'migl606r.sqr'").fields(0)
'--- The following only returns a record count of 1?
Set rstTemp = db.OpenRecordset("Select * From [Qry-Export Summary] Where LAYOUT_ID = 'migl606r.sqr'")
End Sub
dovholuk