Getting rid of #error in txt box

[d9pierce1]

Hello All,


I have an unbound txtBoxes that has the following code in the control source, Unbound TxtBox, Currency Format

=Nz(DLookUp("AcctBalance","qryAccountBalance","AccountID=" & [CboToAccount].[Column](0)),0)

And when CboToAccount is Empty or Null, then i get an #error in the txt box. What do i need to add to this to get rid of the #error?
Thanks

[pbaldy]

Wrap the combo reference in the Nz() function.

[June7]

Referencing Column(0) is probably just the value held by combobox, assuming BoundColumn is first field - so don't need the Column(0).

If no selection has been made, the field is Null and AccountID=Null fails. So yes, the correct place to use Nz() is around the combobox reference.

Now, if you want the textbox to show 0 instead of Null result of DLookup(), use Nz() also around DLookup() (as you are already).

[d9pierce1]

Thanks all,
Just to be clear it should look like this?

=Nz(Nz(DLookUp)("AcctBalance","qryAccountBalance","AccountID=" & Nz([CboToAccount])),0)

OR
=Nz(Nz(DLookUp("AcctBalance","qryAccountBalance","AccountID=") & Nz([CboToAccount])),0)

And this will give me a value of 0 (Which is what i seek) Correct if the combo is null or empty?
Thanks
Dave

[pbaldy]

Not quite, and I would disambiguate the inner Nz():

=Nz(DLookUp("AcctBalance","qryAccountBalance","Acc ountID=" & Nz([CboToAccount],0)),0)

[d9pierce1]

Thank you so much! I would have never guessed at that...
I will mark solved then...
Again,
Thank you

[pbaldy]

Happy to help!