IIF Dlookup in a Query - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

IIF Dlookup in a Query

Peter van Soest

4 years ago

In a customer query I want to (also) select the partner of the customer if there is one.

This code works fine if there IS a partner.
DLookUp("achternaam";"klantenT";"klantenT.klantID=" & [fiscaalpartner])

When there is no partner I get an #error.
I thought with an IIF statement I can determine whether or not there is a partner, and if so select him/her:

fiscp: IIf(Not IsNull([fiscaalpartner]);DLookUp("achternaam";"klantenT";"klantenT.klantID=" & [fiscaalpartner]);0)

But now I get an #error when there IS a partner and a 0 when there there isn't

What am i doing wrong?

	Juan C Rivera @Reply 4 years ago
Developer 17 covers this DLookup Plus May help solve the question.

	Scott Axton @Reply 4 years ago
What you need is to use the NZ Function with your DLookup inside the IIF Statement.

	Peter van Soest OP @Reply 4 years ago
tnx @scott, this one did the job :) PartnerBSN: IIf(Nz([fiscaalpartner];0)>0;DLookUp("fiscaalnummer";"klantenT";"klantenT.klantID=" & [fiscaalpartner]);"")

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View