Text Box with #Error, How can I get ride of it.

[AccessHope]

Friends:
I have Two control Boxes(Field1 and Field2). Field1 text Box wit expression Look at Database table field using Dlookup() function,

Field1 expression like this:
=NZ(DLookUp("[up_rt_cp_only_flag]","[SubType2]","[SubType2].[Single_Name] ='" & [TextRDW39R] & "'"))


Field2 text box caculate value based on the Field1's value wich return Dlookup() function.
Field2 expression like this:
=IIf(NZ([Field1])="Y","Yes","No")

It caculate at form load process. it works fine, Field1 never show error which means it return value correct, I don't know if it is null value in table field cause this problem, so i used NZ function.

However, some time it works fine, many time I got #error for the field2.


Do you have any idea?


Thank you for your help.

 

[FoFa]

But you didn't complete your NZ function by your example:
NZ([column],default)
Try this:
=NZ(DLookUp("[up_rt_cp_only_flag]","[SubType2]","[SubType2].[Single_Name] ='" & [TextRDW39R] & "'"),0)

 

[AccessHope]

Hi, FoFa:
Thank you so much for your replay. Yes, I tried followed your suggestion, still got error.

the problem is when the form is loading, those control box every time load those text box with different order. I noticed, for example: if Field2 load before Field1, It show #error in Field2 box. Is there any way I can set up their loading order on a form?

Thanks