Finding data w/ more than 2 decimals

[pdbowling]

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

 

[KenHigg]

myNumber - format(myNumber,2) <> 0

???
kh

 

[Samoan]

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.

 

[DALeffler]

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,

 

[DALeffler]

Heh!!

Try:

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

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

 

[WayneRyan]

pd,

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

Wayne

 

[DALeffler]

Nope...

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

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

...

 

[WayneRyan]

Oops,

Flopped the ">" and "<".

Wayne