public function always returns zero

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

Hi all,

I have a public function that performs some simeple calculations but when i
call the function from another part of the code it always returns zero
despite it makes the calculations right. can somebody show me the problem?
herebelow i include the function code and the calling code:

Public Function calc_load_amnt(n As Integer, v_cov_id As Long) As Currency

Dim l_first As Currency
Dim l_each As Currency
Dim result As Currency

If IsNull(DLookup("[assl_amount]", "t_assoc_loadings", "[assladd_rule] = 1
AND [asscov_id] = " & v_cov_id)) = True Then
l_first = 0
Else
l_first = DLookup("[assl_amount]", "t_assoc_loadings", "[assladd_rule] =
1 AND [asscov_id] = " & v_cov_id)
End If

If IsNull(DLookup("[assl_amount]", "t_assoc_loadings", "[assladd_rule] = 2
AND [asscov_id] = " & v_cov_id)) = True Then
l_each = 0
Else
l_each = DLookup("[assl_amount]", "t_assoc_loadings", "[assladd_rule] =
2 AND [asscov_id] = " & v_cov_id)
End If

result = l_first + (n * l_each)

calc_load_amnt = result

l_first = Empty
l_each = Empty
result = Empty
End Function
............
'calling procedure
Private Sub install_num_AfterUpdate()
Dim variab As Currency

varaib = calc_load_amnt(Me.install_num, Me.cov_id)
MsgBox variab 'Here i get zero instead the actual result

variab = Empty
End Sub
 
Peter said:
Hi all,

I have a public function that performs some simeple calculations but when
i
call the function from another part of the code it always returns zero
despite it makes the calculations right. can somebody show me the problem?
herebelow i include the function code and the calling code:

Public Function calc_load_amnt(n As Integer, v_cov_id As Long) As Currency

Dim l_first As Currency
Dim l_each As Currency
Dim result As Currency

If IsNull(DLookup("[assl_amount]", "t_assoc_loadings", "[assladd_rule] = 1
AND [asscov_id] = " & v_cov_id)) = True Then
l_first = 0
Else
l_first = DLookup("[assl_amount]", "t_assoc_loadings", "[assladd_rule]
=
1 AND [asscov_id] = " & v_cov_id)
End If

If IsNull(DLookup("[assl_amount]", "t_assoc_loadings", "[assladd_rule] = 2
AND [asscov_id] = " & v_cov_id)) = True Then
l_each = 0
Else
l_each = DLookup("[assl_amount]", "t_assoc_loadings", "[assladd_rule] =
2 AND [asscov_id] = " & v_cov_id)
End If

result = l_first + (n * l_each)

calc_load_amnt = result

l_first = Empty
l_each = Empty
result = Empty
End Function
...........
'calling procedure
Private Sub install_num_AfterUpdate()
Dim variab As Currency

varaib = calc_load_amnt(Me.install_num, Me.cov_id)
MsgBox variab 'Here i get zero instead the actual result

variab = Empty
End Sub
Click to expand...


Is your calling code posted above a copy/paste of the real code, or did you
retype it into this message. If it's a copy/paste, you have a typo:
varaib = calc_load_amnt(Me.install_num, Me.cov_id)
Click to expand...

Note the spelling: "v a r a i b".
Your declared variable is: "v a r i a b".

Incidentally, these lines in your function:
l_first = Empty
l_each = Empty
result = Empty
Click to expand...

.... accomplish nothing, and should be removed.
 
Here is a simpler version of your function. It uses the DLookup function
only twice instead of four times.

Public Function calc_load_amnt(n As Integer, v_cov_id As Long) As Currency

Dim l_first As Currency
Dim l_each As Currency

l_first = Nz(DLookup("[assl_amount]", _
"t_assoc_loadings", _
"[assladd_rule] = 1 AND [asscov_id] = " & _
v_cov_id),0)

l_each = Nz(DLookup("[assl_amount]", _
t_assoc_loadings", _
"[assladd_rule] = 2 AND [asscov_id] = " & _
v_cov_id),0)

calc_load_amnt = l_first + (n * l_each)

End Function
 
Back
Top