Unanswered: Cannot call function with optional parameters from query with null data
I have a function that performs some calculations on two numbers and returns the results.
The data tables have blank values for some of the records. (I cannot update the records in this dataset)
If I try to call this function from a query I get #Error returned for the rows with null values. I have to surround the field names in the query with nz([field name],0) in order for the calculations to work correctly.
I have tried setting both of the function parameters as optional with default values of zero. I also tried using the nz call inside the function to handle null values that are passed in.
Is there a way to avoid the need for the nz call inside the query? This function will be used by several people who are ok with access but not great and I would like to be able to just call the function as calculate([field1],[field2]))
One way or the other you need to account for the nulls. You can call the function the way you want as long as you test for/replace nulls within it. Note that the parameters would have to be Variants to be able to pass nulls to the function.
using the variant datatype will be slower than the native datatype, it may not be noticably slower but it is somethign to bear in mind. extensive use of user defined function is queries that process large amounts of data can significnatly affect throughput.
No doubt doing the null checking inside the function makes more sense then having to put a NZ test before each call to the function.