VB Loop thu record ansd loop thu table

[pekajo]

Hi,

I need to check each field in a record for data as I loop thu a table. (recordset)
For example
Record looks like:
Field1 Field2 Field3 Field4 etc - Field names
G Smith 23 Blackburn
H Jones 13 Riverside
J Black 123 Campbell
etc
I need to check if there is data in each field for each record while scanning the table.
So I would think that there will be a loop within a loop.
Thanks for any help
Peter

 

[MarkK]

Yes, you are correct, there would be a loop within a loop. So you'd open the recordset and loop through every record, and then for each record you'd loop through every field in the Recordset.Fields collection. Then you can do a check on each field. That, however, will have poor performance.

What would be faster is to use a query that sums a count of fields that are null. Maybe ...

SELECT Sum(IIf(IsNull([Name]),1,0)) AS NullNameCount, Sum(IIf(IsNull([Value]),1,0)) AS NullValueCount
FROM tTestData;

... or a query like that that actually does whatever you were going to do directly, without using a recordset. That's always faster.

And you can always select just those rows that have nulls for sure with a WHERE clause like ...

WHERE Field1 Is Null Or Field2 Is Null Or Field3 Is Null

... so then, whether you use a recordset or not, you will only be dealing with rows that you know have at least one null.

Hope that helps,

 

[DavidAtWork]

What's your reason for checking, if it's just so you can manage NULL fields during the processing, then you need to have a default value for each data field and use
If IsNull(rs.fields(1)) Then
MyVar= default value
Else
MyVar = rs.fields(1)
End If

David