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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Magius96" <(E-Mail Removed)> wrote in message
news708DA8D-51E9-4FB5-AC1D-(E-Mail Removed)...
>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.

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.

"Magius96" wrote:

> 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.

This works perfect. I thought about using dlookup, but couldn't figure out
how to use it for this particular instance. Thank you.

"Jim Burke in Novi" wrote:

> 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.
>
> "Magius96" wrote:
>
> > 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.