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

[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")

 

[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

 

[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