Simple Addition Fomula

[SDS100UK]

Hi,
I can not understand why this code will not work. Could anyone help??

Public Function AddValues1(value1 As Double, value2 As Double) As Double

If IsNull(value1) Then
AddValues1 = value2
ElseIf IsNull(value2) Then
AddValues1 = value1
End If
AddValues1 = value1 + value2
End Function

What it should do is add 2 fields together, but if 1 of the fields is empty it should return the value of the other populated field, but I simply get the message #Error in the field. If both fields are populated it works!!

TIA

Steven

 

[PaulMilnes]

Steven

It seems to me that your code makes it possible for value2 to be assigned if it's null, as you haven't checked it. This would give an error, which wouldn't be triggered if value2 is populated.

Perhaps you should do a general check first, to determine if both values are null.

HTH

Paul

 

[Aivars]

Hi, Steven!

If IsNull(value1) Then
AddValues1 = value2
ElseIf IsNull(value2) Then
AddValues1 = value1
End If
AddValues1 = value1 + value2 'This command row can be included Null value(s). Because proceed error.

>>>> In addition I seem overall function illogical (see at blue).

Aivars

 

[RoyVidar]

Hi!

Aivars is giving you the clue, but not the answer. Your trouble is: nomatter how well you check value1 and value2, you still assign the function the value of both variables in the functions last statement. Try this:

Public Function AddValues1(value1 As Double, value2 As Double) As Double

If IsNull(value1) Then
AddValues1 = value2
ElseIf IsNull(value2) Then
AddValues1 = value1
Else
AddValues1 = value1 + value2
End If
End Function

Roy-Vidar

 

[JohnLowell]

Also, how can you pass in a null value if your variables are declared Double. You may want to change the function variables to Variant to get the function to work.

 

[raskew]

Don't believe a user-defined function is necessary. Instead, use the NZ() function.

For example, if you had tblDoubles which looked like this (please excuse the formatting):

value1 value2
21
45
21 45


…this query:

SELECT value1, value2, nz([value1],0)+nz([Value2],0) AS thesum
FROM tbldoubles;

…would result in this:

value1 value2 thesum
21 21
45 45
21 45 66