Limiting Results to 2+ Decimal Places

  • Thread starter Thread starter meangene
  • Start date Start date
M

meangene

How would I limit results of a number field to values that have values > 2
decimal places (e.g., 12.034 would show but 13.08 would not). How would be
best way to do this. Thanks all!
 
In fact, 13.08 is 13.080, so there is three decimals.... or four, or, well,
there is an infinity of decimal, all zeros. And before you say, 'except
zeros', then, you mean you have no number between
17.079 and 18.001 ?



Vanderghast, Access MVP
 
I wasn't clear enough. I have a standard cost field that has cost values
ranging from 2 - 6 decimal places - cost values should only be out to 2
decimal places. In order to assist our ERP tech support, I need to run a
querie that identifies all standard cost values that have values displaying
out beyond 2 DP. In the example you gave below, we really would have one item
with a cost of 13.08 and another with 13.080: its the second one I want to
grab. Thanks!
 
meangene said:
How would I limit results of a number field to values that have values > 2
decimal places (e.g., 12.034 would show but 13.08 would not). How would be
best way to do this.
Click to expand...


You can use a WHERE clause like:

Int(thefield * 100) <> (thefield * 100)
 
I wasn't clear enough. I have a standard cost field that has cost values
ranging from 2 - 6 decimal places - cost values should only be out to 2
decimal places. In order to assist our ERP tech support, I need to run a
querie that identifies all standard cost values that have values displaying
out beyond 2 DP. In the example you gave below, we really would have one item
with a cost of 13.08 and another with 13.080: its the second one I want to
grab. Thanks!
Click to expand...

If this is a Number field YOU CAN'T.

13.08 and 13.080 and 13.0800000000000 are *exactly the same number*, stored
with exactly the same bits in the Decimal or Double field.

If the datatype is Text then you can compare the length of the string after
the decimal:

Len(Mid([cost], InStr([cost], ".")))

will be 3 for .08 and 4 for .080. But again, this will not apply if the field
is any type of Number (or a Currency field).

If this data is user entered, you could use an Input Mask to force entry of
only two digits, at the cost of some inconvenience to the user.
 
Back
Top