Dlookup Problem!

[papic1972]

Hi all,

I use the following DLookup statement as a control source in a text box:
=(DLookUp("[SumofPaymentReceived]","[qryDlookupDebtPrelimTOTAL]","[ID]= " & [id]))

If the Dlookup returns no records from qryDlookupDebtPrelimTOTAL, how can make my textbox show a "0" value. I tried using a Public Function on the form & then point back to it from the control source, but i couldn't get it to work.
Can anyone offer some assistance.

Thanks.

 

[papic1972]

Not to worry, i've figured out the problem with the Public Function, it works now.

 

[Remou]

Nz is good for Nulls:

[tt]=Nz(DLookUp("[SumofPaymentReceived]","[qryDlookupDebtPrelimTOTAL]","[ID]= " & [id]),0)[/tt]

 

[papic1972]

Thanks Remou!

 

[KornGeek]

I agree with Remou.

I never use DLookup (or DCount, etc.) without using Nz to define what value to use if there are no records.

Well, maybe "never" is too strong of a word. It is conceivable that I might accept NULL as a valid response, but I can't think of any times when I've intentionally done that.

 

[PHV]

I "never" saw DCount returning a Null ...

 

[KornGeek]

PHV,

you're probably right. However, long ago I decided to err on the side of caution. Calling Nz with a 0 returns the 0, and you have no problem. Getting a null can be a problem. Thus, I probably tend to overuse Nz.