Simple function call

  • Thread starter Thread starter Reidar
  • Start date Start date
R

Reidar

I have a form with 3 fields
field1, field2 and field3
Field3 should be the sum of field 1 and field2

The code is:
Function test(Tall1 As Double, Tall2 As Double) As Double
test = Tall1 + Tall2
End Function

Private Sub btnTest_Click()
Dim a As Double
Dim b As Double
Dim c As Double

a = Me.Field1
b = Me.Field2
c = test(a, b)

'*** error ***
Me.Field3 = c
'***********
End Sub

Why do I get an error when I try to get the result into field3 ?
reidarT
 
Do you have values in field1 and field2? If either is Null, this could be
causing the error. Try this version:

Function test(Tall1 As Double, Tall2 As Double) As Double
test = Nz(Tall1,0) + Nz(Tall2,0)
End Function
 
That won't work.

Function test(Tall1 As Double, Tall2 As Double) As Double

needs to be

Function test(Tall1 As Variant, Tall2 As Variant) As Double
 
I have changed from double to variant and both fields have values. I still
get the errormesage
'Argument not optional'

reidarT
 
Reidar,

Do you have Option Explicit turned on? If not, turn it on and compile the
app.

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------
 
You are correct; however, this does:

Function test(Optional Tall1 As Variant, Optional Tall2 As Variant) As Double
Tall1 = IIf(IsMissing(Tall1), 0, Nz(Tall1, 0))
Tall2 = IIf(IsMissing(Tall2), 0, Nz(Tall2, 0))
test = Tall1 + Tall2
End Function
 
Since it's apparently always being called as c = test(a, b), I see no reason
to make the arguments optional.

If you do want to go that route, the following may be simpler:

Function test(Optional Tall1 As Variant = 0, Optional Tall2 As Variant = 0)
As Double
test = Nz(Tall1, 0) + Nz(Tall2, 0)
End Function
 
Back
Top