Forum Discussion
What's the cleanest way to skip NULLs when looping through Recordest in VBA?
I've been working on a legacy Access app, and I find myself looping through a DAO recordest to process a bunch of fields. The thing is , some of those fields occasionally contain NULLs, and I've noticed they can easily break logic when you're not careful, especially when doing string concatenation, arithmetic, or conditional checks.
I'm doing something like this :
If Note IsNull(rs!MyField) Then
' Do something with rs!MyField
End If
Which works, but when you've got multiple fields or are writing more compact logic, it starts to get messy real fast. I'm wondering. Is there a cleaner or more elegant way to handle or skip NULLs ?
PS ; I am having issues with the code editor
2 Replies
In VBA the cleanest way is to wrap your field access with Nz() (Access) or a helper function, so you don’t have to sprinkle IsNull everywhere
Dim val As String val = Nz(rs!MyField, "") ' returns "" instead of Null
or for numbersval = Nz(rs!Amount, 0)If you want to skip nulls entirely, a simple patterns is
If Not IsNull(rs!MyField) Then ' process it End If
But in practise, using Nz() or a customer safeVal() function keeps the code much cleaner when looping over multiple fields.There are two components to the problem.
First, if your data has nulls scattered through different fields in different records, the way you handle that is pretty much what you are doing now, writing defensive code to handle those nulls.
Second, the a priori question is why does the data include nulls? Obviously, the process is such that you allow for them. However, it is sometimes the case that a better approach is to structure the data so that nulls are not allowed. Without knowing more about your particular business rules and data, it's not possible to be more specific, yet I'd be looking at whether you can avoid them in the first place.And finally, instead of running your process directly on the table with nulls, perhaps you could first create a query to use Nz([FieldName], 0) or another appropriate Nz() expression to pre-handle the nulls before they are encountered in this recordset process.
