Looping through fields in a query 1

[daklem]

Hi all,

Is there a way to loop through the fields in a DAO recordset without refering to the field names. I would like to be able to determine the number of fields in the recordset and loop through all the fields checking for nulls on the fly.

Thank you for your help.

 

[randy700]

If you don't refer to the field names, how will you know where the null is if/when you find one?


Randy

 

[daklem]

randy 700

I just need to know if there are any nulls, not which field is null

 

[Golom]

Dim rs As DAO.Recordset
Dim fd As DAO.Field
Set rs = db.OpenRecordset("Select * From tbl")

For Each fd in rs.Fields
   If IsNull(fd.Value) Then
      [COLOR=black cyan]' Found a Null in field[/color]
   End If
Next fd

 

[jebry]

Hi!

Like this:

Dim fld As Field
Dim rst As DAO.Recordset
Dim strFields As String
Dim intCounter As Integer

intCounter = 0
strFields = ""
Set rst = CurrentDb.OpenRecordset("YourQuery", dbOpenDynaset)

For Each fld In rst.Fields
intCounter = intCounter + 1
If IsNull(fld.Value) = True Then
strFields = strFields & fld.Name & ";"
End If
Next fld

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[daklem]

Golom,

Thanx, I will try that and let you know how it works.

 

[Golom]

Of course, that looks at only the first record of the recordset. To look at all of them you would need

Dim rs As DAO.Recordset
Dim fd As DAO.Field
Set rs = db.OpenRecordset("Select * From tbl")

Do Until rs.EOF
   For Each fd in rs.Fields
      If IsNull(fd.Value) Then
         Debug.Print "Field " & fd.Name & " IS NULL" 
      End If
   Next fd
   rs.MoveNext
Loop

 

[daklem]

Thank you Golom and Jerby,

Just what I needed