Hi.
I have a calculated field in my query but when I select the query the field contains #Error.
I then change to design view and re-run the query and the field calculates properly.
Is there a way to ensure the field calculates without having to run the query twice please.
The calculation is:
SYS: Int(IIf(Val([ClCh])<=2 And Val([GCh])<=3 And Val([NetCh])<=9 And [ResN]<=4 And Val([DCh])<=2 And Val([DCh])>=-4,IIf(Val([ClCh])<=1 And Val([NetCh])<=3 And [ResN]<=3,IIf(Val([ClCh])<=0 And Val([NetCh])<=0 And [ResN]<=3,Val([B2_Q4_Lookups]![CrsCh])/2-Val([B2_Q4_Lookups]![NtCh])+12/Val([ResN])-Val([ClCh])+0.5,Val([B2_Q4_Lookups]![CrsCh])/2-Val([B2_Q4_Lookups]![NtCh])+6/Val([ResN])-Val([ClCh])+0.5),Val([B2_Q4_Lookups]![CrsCh])/2-Val([B2_Q4_Lookups]![NtCh])/2+4/Val([ResN])-Val([ClCh])+0.5),0))
Many thanks for your thoughts on this.
I have a calculated field in my query but when I select the query the field contains #Error.
I then change to design view and re-run the query and the field calculates properly.
Is there a way to ensure the field calculates without having to run the query twice please.
The calculation is:
SYS: Int(IIf(Val([ClCh])<=2 And Val([GCh])<=3 And Val([NetCh])<=9 And [ResN]<=4 And Val([DCh])<=2 And Val([DCh])>=-4,IIf(Val([ClCh])<=1 And Val([NetCh])<=3 And [ResN]<=3,IIf(Val([ClCh])<=0 And Val([NetCh])<=0 And [ResN]<=3,Val([B2_Q4_Lookups]![CrsCh])/2-Val([B2_Q4_Lookups]![NtCh])+12/Val([ResN])-Val([ClCh])+0.5,Val([B2_Q4_Lookups]![CrsCh])/2-Val([B2_Q4_Lookups]![NtCh])+6/Val([ResN])-Val([ClCh])+0.5),Val([B2_Q4_Lookups]![CrsCh])/2-Val([B2_Q4_Lookups]![NtCh])/2+4/Val([ResN])-Val([ClCh])+0.5),0))
Many thanks for your thoughts on this.