Recordsets, indexes, and null values

[TheJim01]

I'm using VBA & ADODB to maneuver through a recordset that contains a single
record.

All variables are defined per Option explicit, and are all properly initiated.


When I run the SQL statement in SQL view of an Access Query, I get a single
record with 7 fields. Each field contains the correct (non-null) value.

When I run the below code, I get message boxes that contain "*field name* = "
-- the value is null.

===== Code =====

Dim rs as ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open sqlstr, conn, adOpenDynamic, adLockReadOnly

If Not rs.EOF Then

rs.MoveFirst

For i = 0 To 6
MsgBox rs.Fields(i).Name & " = " & rs.Fields(i).Value
Next i

rs.close

End If

===== End Code =====

Oh, and the kicker is: this code worked yesterday.

So what gives? My connection must be OK, otherwise the query would be
bombing at the .Open command. I'm obviously receiving results, because it's
delivering message boxes. But where did my values fall out? Like I said,
I'm receiving results when I run the SQL outside VBA...

 

[TheJim01 via AccessMonster.com]

The problem actually was in the SQL query, though I don't know why it was a
problem. I had an aggregate function (AVG) in my query, and was filtering by
"WHERE" clauses. Even if I used "GROUP BY"/"HAVING" instead, it would still
return null values. So apparently I can't filter an aggregate pushed through
ADODB? Doesn't makes sense, since I was doing it just a couple days ago. If
anybody knows why this phenomenon may be occurring, please let me know.
Thanks!