Finding data w/ more than 2 decimals

pdbowling

Registered User.
Local time
Today, 12:02
Joined
Feb 14, 2003
Messages
179
Hi all,
I was wondering if someone could tell me what criteria to put in at design view for queries on a number field when you want to mine out numbers with more than 2 decimal places.
Thanks everyone.
PB
 
myNumber - format(myNumber,2) <> 0

???
kh
 
You could try this:

SELECT *
FROM MyTable
WHERE len(mid([MyTable].[MyNumber],instr([MyTable].[MyNumber],".")+1)) > 2;

It would probably be easier to do this in the SQL view as opposed to the design view.
 
How about:

WHERE Len([MyTable]![MyNumber]) - InStr(1, [MyTable].[MyNumber], ".") > 2

which should have the exact same result as the solution Samoan posted.

Neither solution takes into account if the number doesn't have a decimal point to begin with: Both will return true if the MyNumber field doesn't contain a decimal point (I get errors if the number doesn't contain a decimal...)

How I would do it is like this:

WHERE InStr(1, Str([MyTable]![MyNumber]), ".") > 0 And Len(Str([MyTable]![MyNumber])) - InStr(1, Str([MyTable].[MyNumber]), ".") > 2

hth,
 
Heh!!

Try:

WHERE InStr(1,[MyTable].[MyNumber] * 100, ".") > 0

(too many trees in the way of the forest...)
 
pd,

Code:
SELECT *
FROM MyTable
WHERE Int(MyNumber]*100) > MyNumber * 100

Wayne
 
Nope...

? Int(168.001*100) > 168.001 * 100
False

? Int(168.01*100) < 168.01 * 100
True

...
 
Last edited:

Users who are viewing this thread

Back
Top Bottom