Access - DLookup error for empty fields

1
votes

I have a DLookup function for displaying records from another table. Function works o.k., but problem occurs when there is empty field in the parent record.

Here is my DLookup function :

=DLookUp("Surname";"Employees";"ID_Employees_Table2=" & [Forms]![Company]![Company_Subform].[Form]![ID_Employees_Table1])

So, this works when ID_Employees_Table1 field is not empty, but If is "empty" It returns me #Error.

I tried with Nz function like this :

=Nz(DLookUp("Surname";"Employees";"ID_Employees_Table2=" & [Forms]![Company]![Company_Subform].[Form]![ID_Employees_Table1]);0)

But Access still returns me #Error in Textbox - where I'm using this function. I've tried also in combination with IsNull, IIf, but both can't stop this #Error from appearing.

How on earth can I stop this error from appearing in my Textbox, please any advices !

1
ms-accessms-access-2010
sorry, I was too fast. I got It working, answer is in my question. - LuckyLuke82

1 Answers

2
votes

Answer is -

=DLookUp("Surname";"Employees";"ID_Employees_Table2=" & Nz([Forms]![Company]![Company_Subform].[Form]![ID_Employees_Table1]);0))

I just had to put Nz function in correct place, nightmare !!