Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    2

    Unanswered: catching an error

    Hi here is my query, the fonction that goes with it. It gives an error for the rows that don't have a date. Why does not the switch statement gets it?

    Thanks,

    SELECT tableTest.id, tableTest.maturity, switch([maturity] Is Null,0,True,test([maturity])) AS Expr1
    FROM tableTest;


    Function test(Optional mat As Date) As Variant
    test = mat / 365
    End Function

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    Switch evaluates all of the expressions, even though it returns only one of them. For this reason, you should watch for undesirable side effects. For example, if the evaluation of any expression results in a division by zero error, an error occurs.

    It has to do with your data typing in the Function. per the MS Help (above) 'switch' is evaling ALL expressions for each record. The Nulls are throughing an error in your function.

    Ans. modify your function to process each record and check whether its null or a date, then just call the function and don't bother with switch.

    OR use the IIF function insted of Switch:


    This worked flawlessly with your test() funcation as is.

    SELECT tableTest.id, tableTest.maturity, IIF([maturity] Is Null,0,test([maturity])) AS Expr1
    FROM tableTest;


    .
    ~

    Bill

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Try this

    SELECT tableTest.id, tableTest.maturity, IIF(ISNull([maturity]), 0, test([maturity])) AS Expr1
    FROM tableTest;

  4. #4
    Join Date
    Oct 2004
    Posts
    2

    Thanks!

    it works great!
    cheers.

Posting Permissions

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