Need to run Query twice to clear Error (1 Viewer)

caprice63

Registered User.
Local time
Today, 12:45
Joined
Nov 4, 2018
Messages
25
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:45
Joined
May 7, 2009
Messages
19,169
it is better to create a UserDefine Function if you have lots of IIF()s in there.
Code:
Public Function fnCalCulate(ClCh As Variant, GCh As Variant, NetCH As Variant, ResN As Variant, DCh As Variant, CrsCh As Variant, NtCh As Variant) As Integer
ClCh = Val(ClCh & "")
GCh = Val(GCh & "")
NetCh = Val(NetCh & "")
ResN =Val(ResN & """)
DCh = Val(DCh & "")
CrsCh = Val(CrsCh & "")
NtCh =Val(NtCh & "")
fnCalCulate=0
IfClCh<=2 And GCh<=3 And NetCh<=9 And ResN<=4 And DCh]<=2 And DCh>=-4 Then
		If ClCh]<=1 And NetCh<=3 And ResN<=3 Then
			If ClCh<=0 And NetCh<=0 And ResN<=3 Then
					fnCalculate = CrsCh/2-NtCh+12/ResN-ClCh+0.5

			End If
		Else
			fnCalculate = CrsCh/2-NtCh+6/ResN-ClCh+0.5
		End If
Else
	fnCalculate = CrsCh/2-NtCh/2+4/ResN-ClCh+0.5
End If
End Function

Sys: fnCalCulate([ClCh], [GCh], [NetCH], [ResN], [DCh], [CrsCh], [NtCh])
 

caprice63

Registered User.
Local time
Today, 12:45
Joined
Nov 4, 2018
Messages
25
Thanks.
I've not used user-defined functions. I'll do some homework then try it out and let you know.
Thanks for the reply.
Chris
 

caprice63

Registered User.
Local time
Today, 12:45
Joined
Nov 4, 2018
Messages
25
Thanks again.
I've managed to make a user defined function - much easier than nesting my IF's.
Cheers for the tip.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:45
Joined
May 7, 2009
Messages
19,169
cheers, and its more easier to see the logic and formula.
 

Users who are viewing this thread

Top Bottom