Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264

    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]))

    Thanks.

    Steve

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •