Min() with recordsets

[xenou]

I found an interesting and unexpected result today.

Given an aggregate query that I expected to return no records, I was actually given back a recordset with a null record.

Here's an example:
Fill a test table (Table1) with values 1,2,3 - lets call the field "ID"

Run a query selecting values greater than 3:
SELECT ID FROM Table1 WHERE ID > 3;
^^ returns EOF

Now run a query selecting Min() values greater than 3:
SELECT Min(ID) FROM Table1 WHERE ID > 3;
^^ returns Null

Is this behavior something I should have expected? It's new to me (I tested this with ADO and DAO recordsets).

Here's some test code if you'd like a quick demo (yes, I even create the table for you )

[COLOR="Navy"]Sub[/COLOR] Test()
[COLOR="SeaGreen"]'Assumes a table called Table1[/COLOR]
[COLOR="SeaGreen"]'In the table create a single field ID with three records {1,2,3}[/COLOR]
[COLOR="Navy"]Dim[/COLOR] rs [COLOR="Navy"]As[/COLOR] DAO.Recordset
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

    [COLOR="SeaGreen"]'//Create our test table[/COLOR]
    [COLOR="Navy"]Call[/COLOR] CreateTable_Table1
    
    [COLOR="SeaGreen"]'//Run query 1[/COLOR]
    s = "SELECT ID FROM Table1 WHERE ID > 3;"
    [COLOR="Navy"]Set[/COLOR] rs = CurrentDb.OpenRecordset(s)
    [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] String(Len(s), "-")
    [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] s
    [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] "EOF: " & rs.EOF
    rs.Close
    
    [COLOR="SeaGreen"]'//Run query 2[/COLOR]
    s = "SELECT Min(ID) FROM Table1 WHERE ID > 3;"
    [COLOR="Navy"]Set[/COLOR] rs = CurrentDb.OpenRecordset(s)
    [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] String(Len(s), "-")
    [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] s
    [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] "EOF: " & rs.EOF
    [COLOR="Navy"]If[/COLOR] IsNull(rs.Fields(0).Value) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] "Null: " & "True"
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    rs.Close
    
    [COLOR="Navy"]Set[/COLOR] rs = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Sub[/COLOR] CreateTable_Table1()
[COLOR="Navy"]Dim[/COLOR] tdf [COLOR="Navy"]As[/COLOR] DAO.TableDef
[COLOR="Navy"]Dim[/COLOR] idx [COLOR="Navy"]As[/COLOR] DAO.Index
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] My_Exit
    [COLOR="Navy"]Set[/COLOR] tdf = CurrentDb.CreateTableDef("Table1")
    tdf.Fields.Append tdf.CreateField("ID", dbLong)
    CurrentDb.TableDefs.Append tdf
    [COLOR="Navy"]Set[/COLOR] tdf = [COLOR="Navy"]Nothing[/COLOR]
    
    [COLOR="Navy"]Set[/COLOR] idx = CurrentDb.TableDefs("Table1").CreateIndex("PrimaryKey")
    [COLOR="Navy"]With[/COLOR] idx
        .Primary = True
        .Fields.Append .CreateField("ID")
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    CurrentDb.TableDefs("Table1").Indexes.Append idx
    
    [COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] 3
        s = "INSERT INTO Table1 VALUES (" & i & ");"
        CurrentDb.Execute s
    [COLOR="Navy"]Next[/COLOR] i
    
My_Exit: