question about running a certain query in access

[joe_G]

Hello,

I have a database of over 10,000 part numbers that are six characters
long and should be in this format: XXXXXX; however, I noticed many
records in the table as XX XXXX. The part numbers are any letter or
number combination and I'm just using the Xs as an example. Basically,
what query can I run in access to find all instances of records in the
format of XX XXXX

 

[Allen Browne]

Create a query. In the Criteria row under this field, enter:
Like "?? ????"

That will locate a space in the 3rd position.

To locate a space anywhere in the field, use:
Like "* *"

 

[Tom van Stiphout]

On Sat, 18 Jul 2009 20:36:52 -0700 (PDT), joe_G <[email protected]>
wrote:

It should be impossible to enter XX XXXX part numbers, since you
smartly created that field as a Text(6), and set a validation rule on
that field: Len(myField) = 6
OK, to find the offending ones (before you do change that field size)
try this:
(find any record with length not 6)
select * from myTable
where Len(myField) <> 6

or:
(find any record with <space> in the third position)
select * from myTable
where Mid$(myField,3,1) = " "

or:
(find any record with a <space>
select * from myTable
where Length(myField) like "* *"

(of course you replace myObjectNames with yours)

-Tom.
Microsoft Access MVP