Function returns #error in a left join

[Fjordur]

Hi,
I have a query with a LEFT JOIN. One of the fields is a VB function that
takes fields of the joined table. This field returns #error when the joined
records are null. Which makes sense but doesn't look nice on my forms.
How can I display something like an empty string? (apart from using
conditional formating)?
Thanks

 

[Allen Browne]

The field that returns #Error contains an expression.
The expression appears to be invalid when the field in Null.
The solution will therefore be to change the expression so it is valid when
the field is null.

As an example, if the expression is:
DLookup("UnitPrice", "tblPrice", "ProductID = " & [ProductID])
then when the ProductID field is null, the 3rd argument becomes:
ProductID =
which is clearly invalid.

You could solve that problem by using Nz() to supply a value instead of
null, i.e.:
DLookup("UnitPrice", "tblPrice", "ProductID = " & Nz([ProductID],0))