some text-formatted numbers rounded, others not

[storyman]

I have a field formatted as text that mostly contains numbers. All numbers
between 1 and 10 are carried to one decimal place. I want numbers greater
than 1000 to have commas, and numbers less than 1000 to be displayed as is.
This expression:

Expr1: IIf([RESULT]>="1000",Format([RESULT],"#,###"),[RESULT])

accomplishes this with one exception. The exception is that I noticed that
some numbers between 1 and 10 have been rounded with no decimal places, and
others are displayed as is with one decimal place. Any help with this?

 

[Lord Kelvan]

you may need to set a format for the result as well as access may
automatically round it if it is eg 1.0 are you able to give a sample
list of what is in the table

regards
kelvan

 

[storyman]

Here is an example with the comparing the as is (RESULT) with the expression
(Expr1). As you can see, the third row has the rounding effect, while the
fourth row is fine.

SAMPLE DATE RESULT Expr1
MW-1 1/7/2008 1600 1,600
MW-1 5/4/2007 7.9 8
MW-1 7/9/2007 1.8 1.8
MW-1 10/1/2007 <25 <25
MW-1 1/7/2008 110 110
MW-1 5/5/2008 0.65 0.65

 

[storyman]

Further clue, it is doing the rounding effect on numbers greater than 1,
numbers between 0 and 2 are fine.

 

[Lord Kelvan]

you wont be able to do >="1000" on a text field you will have to cast
it as a num

Expr1:
IIf(IsNumeric([result]),IIf(CInt([RESULT])>=1000,Format([RESULT],"#,###"),
[RESULT]),[result])

that one works gives you

Expr1
1,600
7.9
1.8
<25
110
..65

first part checks if it is a number then it casts it as an int checks
if it is > 1000 and outputs results accordinally

regards
kelvan

 

[storyman]

That worked! Brilliant, thanks Lord Kelvan!

 

[Lord Kelvan]

no problem