How to word a specific criteria in a query 1

[JamesBBB]

Hi All,

I need to run a query to only list a specfic format of record within a number of records in a single field.

Basically I need to run the query that will only return details with the following criteria.

1. The length of the field must be 8 characters long
2. The first 2 digits must be characters and not numbers
3. The last 6 characters must be numeric and not numeric

If anyone could help, I would be extremly grateful as I am completely as at a loss.

many thanks

James

 

[Sumitdev]

In Where clause

len(Field) = 8 
AND 
not isnumeric(mid(Field,1,2)) 
AND 
isnumeric(mid(Field,3))

 

[JamesBBB]

Sumitdev

Many thanks for that piece of code.

It was extremely helpfull and helped me get the results I needed.

Much appreciated

James

 

[PHV]

A simpler way:
Like '[A-Z][A-Z]######'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886