Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: Problem with long function.

    Here is my problem. I have a form that allows a user to query information. If [mileage] come back as anthing less that 10000, I get a -.03 but as soon as [mileage] gets to 10000 and above the whole function works. I can't figure out why this would be.

    2000 Miles: IIf([mileage] Between "1" And "16000" And [Year]="2000",0.03,IIf([mileage] Between "16001" And "24000" And [Year]="2000",0.02,IIf([mileage] Between "24001" And "32000" And [Year]="2000",0.01,IIf([mileage] Between "32001" And "40000" And [Year]="2000",0,IIf([mileage] Between "40001" And "48000" And [Year]="2000",-0.01,IIf([mileage] Between "48001" And "56000" And [Year]="2000",-0.02,IIf([mileage]>"56000" And [Year]="2000",-0.03,"0")))))))

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by tjarvas
    Here is my problem. I have a form that allows a user to query information. If [mileage] come back as anthing less that 10000, I get a -.03 but as soon as [mileage] gets to 10000 and above the whole function works. I can't figure out why this would be.

    2000 Miles: IIf([mileage] Between "1" And "16000" And [Year]="2000",0.03,IIf([mileage] Between "16001" And "24000" And [Year]="2000",0.02,IIf([mileage] Between "24001" And "32000" And [Year]="2000",0.01,IIf([mileage] Between "32001" And "40000" And [Year]="2000",0,IIf([mileage] Between "40001" And "48000" And [Year]="2000",-0.01,IIf([mileage] Between "48001" And "56000" And [Year]="2000",-0.02,IIf([mileage]>"56000" And [Year]="2000",-0.03,"0")))))))
    Simple. Look at how you're testing ... You're doing it by strings ... You might have better luck with longs ...

  3. #3
    Join Date
    Mar 2004
    Posts
    361
    can you explain the difference?

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by tjarvas
    can you explain the difference?
    Difference of what??? How the comparisons work?

  5. #5
    Join Date
    Mar 2004
    Posts
    361
    Could you show me how to write that function with "longs"

  6. #6
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    string sorting:
    1
    10
    100
    2
    3
    4
    45
    56
    7

    This will probably cause problems when you are testing the ranges of your variables. try converting them to numbars first
    val(string)

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by tjarvas
    Could you show me how to write that function with "longs"
    What is the type of your mileage column first? Is it a string?

  8. #8
    Join Date
    Mar 2004
    Posts
    361
    The data/type on the field is text.

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by tjarvas
    The data/type on the field is text.
    Ok ... Then you need to do this: Replace each "[Mileage]" with "CLng([Mileage])" and then romove the quotes from around each number comparison Example:

    ...CLng([Mileage]) BETWEEN 1 AND 16000, ....

  10. #10
    Join Date
    Mar 2004
    Posts
    361
    That fixed it. How do you guys know so much. Have you been using access for years. I have never even heard of CLng. Thanks a ton.

Posting Permissions

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