Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Switch(): Numbers & strings (A2K)

    In an Access query I'm playing with the following two expressions:

    1. Switch(GetMyVariable()=1,[strDescription],GetMyVariable()=2,Val([intID]))

    2. Choose(GetMyVariable(),[strDescription],Val([intID]))

    The field "strDescription" is Text and the field "intID" is a Long Integer.

    If GetMyVariable() = 2 then my expression correctly returns the value of the field "intID." But it returns the value as a string. Why isn't it returned as a number? I figured using the Val() function would force the result to be a number but that doesn't appear to work either.

    I can't use Val(Switch...) because if GetMyVariable() = 1 my expression would return the string value of the field "strDescription". And Val(String) = 0.

    Any ideas? Did I explain the problem clear enough?

    Thanks!!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Switch(): Numbers & strings (A2K)

    How are you using the expression?
    What makes you conclude that the expression returns intID as a string?

  3. #3
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Switch(): Numbers & strings (A2K)

    Hi Hans!

    Thanks for helping...AGAIN!

    I'm using the expression to determine which fields to group & sort on in a report.

    I know it's returning a string because when I sort the field in ascending order 10 comes before 2. Which would be correct if the field were a string.

    Any ideas?

    Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Switch(): Numbers & strings (A2K)

    So if I understand correctly, you're using the expression to define a field (column) in a query.

    A field always has a single type, it can't hold mixed types. In other words, either all values are numeric, or all values are strings. Since the expression may return a string value, this forces all values to be strings.

    You can get around this by formatting the numeric values: instead of Val([intID]) use Format([intID],"000000000"). The value 2 would become 000000002 and the value 10 would become 000000010, and these would be sorted correctly.

  5. #5
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Switch(): Numbers & strings (A2K)

    Kudos! Thank you, once again!

    You know, I tried Format(intID,"Standard"). I was close...but it's the leading zero's that make the difference.

    Have a good Election Night!

Posting Permissions

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