making sure there are 2 spaces after decimal in a string

[spastica]

I have a set of records in an Access database, and I would like to check them to make sure there are 2 characters after the decimal point, and also find out if there is a decimal point in the records at all.

I have been trying to do this with the InStr and Right and Left functions, but I haven't had any luck yet - is there a way to do this?

thanks in advance!

 

[shaddow]

You have stored the value as Text values? or as Numbers?

________
George, M

 

[spastica]

as text values...thanks

 

[shaddow]

You can use just Queryes

-this returns all the fields that has 2 decimal places
SELECT  Division from theemp where Instr(Right(Division,3),".")=1

-this will return all the ones that has no decimals
SELECT Division from theemp where Instr(Division,&quot;.&quot;)<=0

-this returns all the fields that has 1 decimal places
SELECT  Division from theemp where Instr(Right(Division,2),&quot;.&quot;)=1

-if you want to see what records dont have exactly 2 decimals you would use this 
SELECT  Division from theemp where Instr(Right(Division,3),&quot;.&quot;)<>1

And so on

________
George, M

 

[foxbox]

1 tiny remark to this: it assumes the numbers are right aligned in the division field; so maybe you need to trim trailing spaces.




hth,
Foxbox