Error# in form using IsNull & Nz

[MeSteve]

I am getting an error using:

=iif (isNull(DLookUp("ContactName","tbl_Contacts","ContactID = " &
DLookUp("ContactID","tbl_ContactProject","RoleID = 2 AND ProjectID = " &
[Forms]![frm_Projects]![ProjectID]))),"",DLookUp("ContactName","tbl_Contacts","ContactID
= " & DLookUp("ContactID","tbl_ContactProject","RoleID = 2 AND ProjectID = "
& [Forms]![frm_Projects]![ProjectID])))

I have also used =Nz(...),"") to no avail. I know there is no record that
matches.

How can I either A: troubleshoot this down to a fix, or B: fix the string to
work?

 

[Duane Hookom]

I would not create an expression that nests this deep. Create a small
user-defined function that accepts the ProjectID and returns the appropriate
value. You can then use this function anywhere you want in your application.
Your maintenance of the "logic" of the expression then stays in one module.