Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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)

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='774294' date='08-May-2009 08:50']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)[/quote]


    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!

Posting Permissions

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