Create a Search Based on Decimal Precision

[jgraves]

The data analysis portion of my job requires me to report on whether numbers
exists in a certain field that have more than 2 decimal places.
For example, I have the following prices in my field called "Price":
1.25
3.434
8.2

Sorting does not work, because that is based on how great or small the
number is, not the precision of the decimal.
Is there any way to create a search that will return "3.434", but not the
other two?
Thanks,
Jen G

 

[Mr B]

Below is an sql statement that will return all records where there are more
than 2 decimal places:

SELECT tblTestData.Product, tblTestData.Cost
FROM tblTestData
WHERE
((Len(Right([tblTestData]![Cost],Len([tblTestData]![Cost])-InStr([tblTestData]![Cost],".")))>2));

The "tblTestData" has the following fileds:
Field Name Data Type
Product Text
Cost Number (Single)

If paste the sql statement into the QBE you will see that it returns the
product name and the cost.

 

[Dirk Goldgar]

The data analysis portion of my job requires me to report on whether
numbers
exists in a certain field that have more than 2 decimal places.
For example, I have the following prices in my field called "Price":
1.25
3.434
8.2

Sorting does not work, because that is based on how great or small the
number is, not the precision of the decimal.
Is there any way to create a search that will return "3.434", but not the
other two?

How about

SELECT * FROM YourTable
WHERE Price <> Round(Price, 2)

?

 

[jgraves]

Hah! I figured out a very simple solution to my own problem. Use wildcard
characters: *.??? in the Find box. Thanks everyone anyway for all the help.

 

[Dirk Goldgar]

Hah! I figured out a very simple solution to my own problem. Use wildcard
characters: *.??? in the Find box. Thanks everyone anyway for all the
help.

Huh! I never would ahve expected that to work, unless your prices were
stored as text, but a quick test shows that it does. Whattaya know! But if
you need to select these records in a query, you may need to use one of the
other methods.