1

I'm joining two tables together. I want to pick one column when their is no data in the other column. One of the feilds is "SumOfHourlykWh" from qdRevenueConsolidatedByHE and the other is "SumOfAverage" from adSCADAConsolidatedByHE.

The function in the new field is: kWh: kWhHE([SumOfHourlykWh],[SumOfAverage])

My VBA code is:

Public Function kWhHE(dRevkWh As Double, dSCADAkWh As Double) As Double

If dRevkWh = "" Then
    kWhHE = dRevkWh
Else
    kWhHE = dSCADAkWh
End If

End Function

Why is putting #Error in the field KWh?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
James Crosbie
  • 13
  • 2
  • 8
  • I'm confused about your intention for the function because `If dRevkWh = "" Then kWhHE = dRevkWh` will attempt to assign an empty string as the function's return value, but the return value is declared `As Double`. Something is wrong there. – HansUp Oct 25 '13 at 20:42

1 Answers1

2

If either [SumOfHourlykWh] or [SumOfAverage] is Null when you call the function in your query ...

kWhHE([SumOfHourlykWh],[SumOfAverage])

... it will trigger an error condition because the function has declared Double for both its arguments. Null is not Double type; and Double variable can not accept Null.

You could avoid that problem by using Nz to substitute another value when the field is Null. If zero is a reasonable substitute for both fields ...

kWhHE(Nz([SumOfHourlykWh], 0), Nz([SumOfAverage], 0))

Alternatively, you could change the function's argument data types from Double to Variant. The function would then accept Null. However you would also then need to revise the function to handle Nulls correctly and to cope with other data types such as String for the function parameters.

HansUp
  • 95,961
  • 11
  • 77
  • 135