rs showing values as null even though they're in the query output!

[Access9001]

After opening a recordset in VBA I use this code to cycle through all fields of all records:

While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
Set fld = rs.Fields(i)
Debug.Print fld.Name, rs(fld.Name)
Next i
rs.MoveNext
Wend

The problem is that some of the values display as null (say, rs("field2") of the third record even though if I open the query manually that cell has a value).

The funny thing is that if I make my query a make-table and then point my recordset to open that instead, the values magically display correctly. Why might this be happening? I need this query to spit out the right values as they are displayed in the query and NOT nulls!

 

[Access9001]

Anyone have any idea? This is a massively annoying problem since I don't want to resort to running messy maketables just because of some Access hiccup.

 

[boblarson]

Are you wanting the VALUE of the field? If so you need to use

rs.Fields(i).Value

as using

rs.Fields(i)

doesn't have a default of .Value like when you refer to a control on a form.

 

[Access9001]

I tried using .value but it still spits back NULL in vba - shows up just fine in the actual query view when I run it manually in Access

 

[Access9001]

Somehow using DAO instead of adodb fixed the issue.

 

[boblarson]

One thing to remember, which I don't know if it would have to do with this at all either is make sure you use explicit declarations.

If you want to use ADO, you use

Dim rst As ADODB.Recordset

or DAO

Dim rst As DAO.Recordset

 

[Access9001]

One thing to remember, which I don't know if it would have to do with this at all either is make sure you use explicit declarations.

If you want to use ADO, you use

Dim rst As ADODB.Recordset

or DAO

Dim rst As DAO.Recordset

Before I was using Dim rst As ADODB.Recordset, but the switch to Dim rst As DAO.Recordset fixed it.