dlookup #error

[Guest]

Hello.

I have a text box where the control source is a dlookup function based on
another text box on the form. When the text box is populated, the
dlookupfunction works fine, but when it is empty, the function returns #error.

I thought that error message meant that there eas something wrong in the
dlookup function, but it cant be all bad since it works when the text box has
a value...?

the dlookup statement looks like this:
=dlookup("[truck_number]";"[supplier]";"[id] = " & forms!transport!driver)

Any ideas?

Baard

 

[Brendan Reynolds]

You can use the NZ function to replace the null value with something else
....

=dlookup("[truck_number]";"[supplier]";"[id] = " &
NZ(forms!transport!driver,0))

This assumes that there are no records with 0 in the id field.

See 'NZ Function' in the help file for more information.

 

[Guest]

That worked perfectly, though I think it was a strange error message.

I use dlookup quite often, but it always seems to cause me new problems
every time.

thx for your help

Baard

You can use the NZ function to replace the null value with something else
....

=dlookup("[truck_number]";"[supplier]";"[id] = " &
NZ(forms!transport!driver,0))

This assumes that there are no records with 0 in the id field.

See 'NZ Function' in the help file for more information.

--
Brendan Reynolds
Access MVP

Hello.

I have a text box where the control source is a dlookup function based on
another text box on the form. When the text box is populated, the
dlookupfunction works fine, but when it is empty, the function returns
#error.

I thought that error message meant that there eas something wrong in the
dlookup function, but it cant be all bad since it works when the text box
has
a value...?

the dlookup statement looks like this:
=dlookup("[truck_number]";"[supplier]";"[id] = " & forms!transport!driver)

Any ideas?

Baard