• Trouble with switch function

    • This topic has 2 replies, 2 voices, and was last updated 16 years ago.
    Author
    Topic
    #459687

    I am using DateDiff to determine the number of days between arrival in Canada and when they show up at the social service agency: FV: Nz(DateDiff(“d”,[DateEntered],[MeetingDate]),0). This is functioning as expected.

    I then use the Switch Function in a new query on FV to determine time frames, less than one year, 1-2 years, etc and I am getting #Error#

    FrstVst: Switch([FV]<=365,"-1 Yr",[FV]<=730,"+1 Yr",[FV]<=1095,"2 to 3 yrs",[FV]<=1825,"3 to 5",True,"More Than 5 Yrs")

    I have used something very similar for age ranges in the past and the syntax is essentially identical, so I'm stumped.

    The only difference is that the age query is using switch for columns in a crosstab and FrstVst is filling in values in a single column. I set the properties for FV in its source query as General Number

    Viewing 0 reply threads
    Author
    Replies
    • #1159748

      I tried your expressions in a query and they worked correctly. Does it make a difference if you define FV as

      FV: Val(Nz(DateDiff(“d”,[DateEntered],[MeetingDate]),0))

      (Nz tends to return a text value instead of a number value)

      • #1159771

        I tried your expressions in a query and they worked correctly. Does it make a difference if you define FV as

        FV: Val(Nz(DateDiff(“d”,[DateEntered],[MeetingDate]),0))

        (Nz tends to return a text value instead of a number value)

        Thanks Hans. that fixed it and I never would have found that one. It’s nice to know that, at least this time, I’m neither crazy nor clueless. Just underinformed!

    Viewing 0 reply threads
    Reply To: Trouble with switch function

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: