dlookup question

  • Thread starter Thread starter ingalla
  • Start date Start date
I

ingalla

I have the following dlookup

=DLookUp("CountOfAPP_ID","qrylookupattend","PAT_ID=" & [PAT_ID])

if there is nothing to look up then the text box is blank, as i would
expect. What i need to happen is that if there is nothing to lookup then the
text box is set to zero.

I have tried setting the default value to zero but it looks as if the
dlookup command is overwritting it.

Can anyone help???

Thanks
Andy
 
=Nz(DLookUp("CountOfAPP_ID","qrylookupattend","PAT_ID=" & [PAT_ID]),0)

If the DLookup does not find a match, a Null is returned. The Nz function
looks at its first argument and if it is Null, it replaces it with the value
in the second argument.
 
ingalla said:
I have the following dlookup

=DLookUp("CountOfAPP_ID","qrylookupattend","PAT_ID=" & [PAT_ID])

if there is nothing to look up then the text box is blank, as i would
expect. What i need to happen is that if there is nothing to lookup then the
text box is set to zero.

I have tried setting the default value to zero but it looks as if the
dlookup command is overwritting it.
Click to expand...


DLookup returns Null when it can't find a matching record.
You can deal with that in a couple ways.

One is to set the text box's Format property to a custom
format code like:
0;;;"0"

Another is to use the Nz function:
=Nz(DLookUp("Coun . . . ID=" & [PAT_ID]), 0)
 
ingalla said:
I have the following dlookup

=DLookUp("CountOfAPP_ID","qrylookupattend","PAT_ID=" & [PAT_ID])

if there is nothing to look up then the text box is blank, as i would
expect. What i need to happen is that if there is nothing to lookup then
the
text box is set to zero.

I have tried setting the default value to zero but it looks as if the
dlookup command is overwritting it.

Can anyone help???

Thanks
Andy
Click to expand...

Use the Nx function and specify 0 as the default return from the function
(2nd argument).

=Nz(DLookUp("CountOfAPP_ID","qrylookupattend","PAT_ID=" & [PAT_ID]), 0)
 
Back
Top