Counting Null fields in VBA

  • Thread starter Thread starter Magius96
  • Start date Start date
M

Magius96

I feel stupid for not being able to figure this out on my own, but here we go:

I have a section of code that I need to bypass if a certain table contains
null values because the user has to manually enter those values before they
can proceed. I have an SQL string that helps me find the records containing
null values, that string is:

SELECT PLD.DEBTOR_FILENO, PLD.ED_PROGRAM_TYPE, PLD.ED_BAL_WHEN_SET,
PLD.DEBTOR_ED_PAYOFF_BAL FROM PLD WHERE (((PLD.ED_PROGRAM_TYPE) Is Null)) OR
(((PLD.ED_BAL_WHEN_SET) Is Null)) OR (((PLD.DEBTOR_ED_PAYOFF_BAL) Is Null));

How can I write a VBA IF statement that will exit the subroutine if there
are any records returned from that SQL statement?

Like I said, it's a simple thing, but it's just eluding me for some reason.
 
Easiest would be to use DCount (or DLookup):

If DCount("*", "PLD", "ED_PROGRAM_TYPE IS NULL " & _
"OR ED_BAL_WHEN_SET IS NULL " & _
"OR DEBTOR_ED_PAYOFF_BAL IS NULL") > 0 Then
Exit Sub
End If
 
I would just do this:

If Not IsNull(DLookup("PLD.DEBTOR_FILENO", "PLD", "PLD.ED_PROGRAM_TYPE Is
Null OR
PLD.ED_BAL_WHEN_SET Is Null OR PLD.DEBTOR_ED_PAYOFF_BAL Is Null")) Then
Exit Sub
End If

This assumes that PLD.DEBTOR_FILENO will always have a value. If it ever has
a value of NULL itself, this wouldn't work. As long as the first parameter
for DLookup is a field that will always have a value regardless of whether
those other ones do or not, that field could be used as the first parameter.
 
This works perfect. I thought about using dlookup, but couldn't figure out
how to use it for this particular instance. Thank you.
 
Back
Top