Tricky Query - Urgent Help

[Guest]

I have approx 600k records+ in a table. The field holds #'s, some of which
have 8 characters, and some which have 9 -
i.e 123456789
23456789
I want to pull the records only with 8 characters. How would i do this?

Thanks,

Nikhil

 

[Dave Patrick]

Try;

SELECT table1.*
FROM table1WHERE (((field1) Between 10000000 And 99999999));

or

SELECT Table1.*
FROM Table1
WHERE (((Len([field1]))=8));

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

 

[Guest]

If the field you have is a number format

SELECT Table1.*
FROM Table1
WHERE ([Field1]<100000000);

HTH
Hafeez Esmail

 

[John Vinson]

I have approx 600k records+ in a table. The field holds #'s, some of which
have 8 characters, and some which have 9 -
i.e 123456789
23456789
I want to pull the records only with 8 characters. How would i do this?

Thanks,

Nikhil

If the field is number, see the other replies; if it's Text, add a
calculated field to the query grid by typing

TxtSize: Len([fieldname])

Use a criterion of 8 on this calculated field.

John W. Vinson[MVP]