Forum Discussion
#Error in unbound text box
Hello Experts,
I am trying to display nothing instead of #Error being displayed in an unbound text box.
Its only showing on the new record row.
The below is not working. It still displays #Error on the new record line.
=IIf(IsError(DLookUp("Beneficiary","[tblLetterOfCredit]","[LCID]=" & [letterOfCreditID])),"",Nz(DLookUp("Beneficiary","[tblLetterOfCredit]","[LCID]=" & [letterOfCreditID]),0))
Do you see where I am wrong?
thank you.
Use Nz to return a non-existing value for letterOfCreditID if it is Null:
=DLookUp("Beneficiary","[tblLetterOfCredit]","[LCID]=" & Nz([letterOfCreditID],0) & "")
5 Replies
If ID was text, Criteria should read:
"[LCID]='" & Nz([letterOfCreditID],0) & "'"However, here it is numeric, so no quotes, but I keep the closing double-quotes to keep the syntax and tell, that nothing is forgotten:
"[LCID]=" & Nz([letterOfCreditID],0) & ""A matter of coding style, I guess. Just like I prefer spelled variables like this and not abbreviated like ltrOfCrdtID
ahh so its a style. Ok. I like hearing expert's different approaches. Thanks for adding that bit of info. I also see an advantage to spelling out instead of abbreviating.
Use Nz to return a non-existing value for letterOfCreditID if it is Null:
=DLookUp("Beneficiary","[tblLetterOfCredit]","[LCID]=" & Nz([letterOfCreditID],0) & "")Hi Gustav! that worked! I notice you added the & "" at the end and I didnt notice any difference between keeping it and striking it. It works in both cases though. I have never seen that & "" part though. Interesting. I will keep it in my book of secrets. I thought NZ was the solution but I was using it wrong (=Nz(DLookUp("Beneficiary","[tblLetterOfCredit]","[LCID]=" & [letterOfCreditID]),0)...wrong placement of the 0. I guess sometimes you need to trap at the individual field level, which is what you did.
. =DLookUp("Beneficiary","[tblLetterOfCredit]","[LCID]=" & Nz([letterOfCreditID],0) & "")
^^^
thanks for the tip George! It makes sense. have a good day.
Break the complex formula down and make sure each of the sub elements returns a value correctly. Then slowly add back elements one-at-a-time.
I'd start with
DLookUp("Beneficiary","[tblLetterOfCredit]","[LCID]=" & [letterOfCreditID])Does that always return a value when used as the control source for the unbound text box?
