# Thread: Problem with long function.

1. Registered User
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. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
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. Registered User
Join Date
Mar 2004
Posts
361
can you explain the difference?

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

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

6. Registered User
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. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
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. Registered User
Join Date
Mar 2004
Posts
361
The data/type on the field is text.

9. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
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. Registered User
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
•