none
Function gives type error when placed inside a Switch statement RRS feed

  • General discussion

  • I have a function which returns a date and it works fine when called from a formula  in a query however when I place the function call inside a switch statement I get an error (presumably a type error).


    Public Function GetEasterSunday(Yr As Integer) As Date
    'Code taken from http://www.cpearson.com/excel/Easter.aspx

        Dim D As Integer
        D = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
        GetEasterSunday = DateSerial(Yr, 3, 1) + D + (D > 48) + 6 - ((Yr + Yr \ 4 + D + (D > 48) + 1) Mod 7)
                
    End Function

    HDate: Switch([HType]='E',GetEasterSunday([Year]),[Htype]='M',[Expr1]-Choose(Weekday([Expr1]),[offset],6,7,1,2,3,4,5),[HType]='F',DateAdd("yyyy",[Year]-2000,[ddmmyyyy]))

    Sunday, August 4, 2019 6:53 AM

All replies

  • Could you create a copy of the database, strip away everything that isn't relevant to the problem, as well as all sensitive information, then zip this copy and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Post a link to the uploaded and shared file in a reply here.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, August 4, 2019 8:34 AM
  • Hello Hans,

    thank you for your interest.

    Here is a link to the access db on google drive

    https://drive.google.com/drive/folders/1AhvmPsE74XJEhS_3XaPI9bkO1pj7tRgq?usp=sharing

    just run the query z_BH_Join

    Sunday, August 4, 2019 8:43 AM
  • HDate: Switch([HType]='E',GetEasterSunday([Year]),[Htype]='M',[Expr1]-Choose(Weekday([Expr1]),[offset],6,7,1,2,3,4,5),[HType]='F',DateAdd("yyyy",[Year]-2000,[ddmmyyyy]))

    Hi Richard,

    When using the Switch function, ALL expressions are evaluated. Has "Expr1" a value? If Expr1 is Null, you will get an error.

    Perhaps you can use the Select statement, that is my favourite. If you want a one-liner, you can wrap the Select statement in a function.

    Imb.

    Sunday, August 4, 2019 9:00 AM
  • Hi Imb

    that's brilliant thank you!. Some of the source table fields where empty so now that i've replace them with values the function is working as expected.

     

    Sunday, August 4, 2019 9:07 AM