How to check if a recordset is empty (or get the count of records) and to get summary values (e.g. max, min, avg) (1 Viewer)

[tcneo]

Hi,

How do I check the following in a recordset (which is from a query, not a table):
1) if it is empty
2) number of records inside it (if i open the recordset with dbOpenDynaset)
3) max, min, avg

I get a type mismatch error if I try the following to get the max:

    Dim myR As Recordset
    Set myR = CurrentDb.OpenRecordset("Query1")
    MsgBox DSUm("Record_value", myR)

 

[June7]

Can't use domain aggregate function on a recordset. DSum() should error as well. Recordset is not needed.

MsgBox DSum("Record_value", "Query1")

 

[Pat Hartman]

You can create a query of the query that does all the aggregate functions at once:

Select Dept, Sum(Expenses) As SumExpenses, Avg(Expenses) As AvgExpenses, Min(Expenses) As MinExpenses, .....
From YourQuery

 

[LarryE]

DCount("*","Query1") will give you the record count in Query1 So:

If DCount("*","Query1") > 0 Then
    Do This
Else
    MsgBox "There are no records"
End If

 

[CJ_London]

for recordsets

1) either check for EOF (rs.EOF) when rs is first opened (if EOF =true then no records) or .movelast and .recordcount
2) use ,movelast then check .recordcount
3) max, min, avg - don't understand this one

 

[Pat Hartman]

And don't forget to count the records ONE query does it all rather than multiple domain functions.

Select Dept, Sum(Expenses) As SumExpenses, Avg(Expenses) As AvgExpenses, Min(Expenses) As MinExpenses, ....., Count(*) As RecCount
From YourQuery
Group by Dept;

 

[June7]

If include Dept or any non-aggregate calc fields in SELECT, need GROUP BY.

Then you pull value from query:

If Not myR.EOF Then
MsgBox = Nz(myR!SumExpenses, 0)
End If

 

[Pat Hartman]

Thanks, June. I fixed the sample query.