Error on Dlookup - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Error on Dlookup

Chris Tyson

2 days ago

I get a #Error when using a Dlookup statement to lookup the Contract status from the ContractT. The contract status is an ID from my HelperT’s description. These are the DLookup’s I’ve tried. Please Help.

Original =DLookUp("Description","HelperT","HelperID=" & Nz((DLookUp("ContractStatusID","ContractT","JobID=" & [JobID])),0))

=Nz(DLookUp("Description","HelperT","HelperID=" & Nz((DLookUp("ContractStatusID","ContractT","JobID=" & [JobID])),0)),0)

=Nz(DLookUp("Description","HelperT","HelperID=" & (DLookUp("ContractStatusID","ContractT","JobID=" & [JobID]))),0)

=DLookUp("Description","HelperT","HelperID=" & (DLookUp("ContractStatusID","ContractT","JobID=" & Nz([JobID],0))))

	Chris Tyson OP @Reply 2 days ago
This happens on a new record

	Chris Tyson OP @Reply 2 days ago

	John Davy @Reply 2 days ago
Hi Chris, On your screen, I see that ContractStatus has # error because it does not yet have data. Since it does not have data, the dlookup fails. John

	Donald Blackwell @Reply 2 days ago
As John said, the best solution might be to wrap your whole DLookup in another Nz to set the value to a blank string: `=Nz(DLookup("Description", "HelperT", "HelperID=" & Nz(DLookup("ContractStatusID", "ContractT", "JobID=" & Nz(JobID, 0)), 0)),"")`

Chris Tyson OP @Reply

2 days ago

Donald The first time I copied your code it stated it was missing and argument (I think that's what is stated). So I tried something else that worked using IIf(IsNull) (See below), which worked. Then I tried your code again and it worked. I must have copied something wrong the first time. Thanks for all your help!!

=IIf(IsNull([JobID]), Null, DLookUp("Description", "HelperT", "HelperID=" & Nz(DLookUp("ContractStatusID", "ContractT", "JobID=" & [JobID]), 0)))

Next Unseen

New Feature: Comment Live View