query for specific data configuration

[Zoe Norleen]

I am pulling data from a linked table. I want to display
the records that have 7 consecutive numbers only in a
primary key field. The field has some entries that are 10
numbers; alpha characters mixed in; alpha only; numerics
with spaces in between; dashes between numerics. To
complicate the matter, the linked table has the value set
for the primary key as a text field.
I have been able to limit the results by using the
criteria Not Like "*a*" And Not Like "*b*" And Not Like "*-
*" And Not Like "*c*" And Not Like "*d*" And Not
Like "*e*" And Not Like "*f*" And Not Like "*g*" And Not
Like "*h*" And Not Like "*i*" And Not Like "*j*" And Not
Like "*k*" And Not Like "*l*" And Not Like "*m*" And Not
Like "*n*" And Not Like "*o*" And Not Like "*p*" And Not
Like "*q*" And Not Like "*r*" And Not Like "*s*" And Not
Like "*t*" And Not Like "*u*" And Not Like "*v*" And Not
Like "*w*" And Not Like "*x*" And Not Like "*y*" And Not
Like "*z*"
but I cannot seem to figure out how to elimate entries
with spaces between the numerics or how to then limit it
to the entries that are only 7 numerics.
Any assistance is appreciated,
Zoe

 

[John Mishefske]

I am pulling data from a linked table. I want to display
the records that have 7 consecutive numbers only in a
primary key field. The field has some entries that are 10
numbers; alpha characters mixed in; alpha only; numerics
with spaces in between; dashes between numerics. To
complicate the matter, the linked table has the value set
for the primary key as a text field.

SELECT
*
FROM
yourTable
WHERE
yourTextField Like "*[0-9][0-9][0-9][0-9][0-9][0-9][0-9]*"

 

[John Mishefske]

I am pulling data from a linked table. I want to display the records
that have 7 consecutive numbers only in a primary key field. The
field has some entries that are 10 numbers; alpha characters mixed in;
alpha only; numerics with spaces in between; dashes between numerics.
To complicate the matter, the linked table has the value set for the
primary key as a text field.

SELECT
*
FROM
yourTable
WHERE
yourTextField Like "*[0-9][0-9][0-9][0-9][0-9][0-9][0-9]*"

Oops - you said 7 consecutive numbers only. Remove the asterisks on each end!

 

[Zoe Norleen]

Thanks John!
I finally got there by using
Like "#######" And Not Like "*a*" And Not Like "*b*" And
Not Like "*-*" And Not Like "*c*" And Not Like "*d*" And
Not Like "*e*" And Not Like "*f*" And Not Like "*g*" And
Not Like "*h*" And Not Like "*i*" And Not Like "*j*" And
Not Like "*k*" And Not Like "*l*" And Not Like "*m*" And
Not Like "*n*" And Not Like "*o*" And Not Like "*p*" And
Not Like "*q*" And Not Like "*r*" And Not Like "*s*" And
Not Like "*t*" And Not Like "*u*" And Not Like "*v*" And
Not Like "*w*" And Not Like "*x*" And Not Like "*y*" And
Not Like "*z*" And Not Like "*" & " " & "*"
Your suggestion is certainly simpler and works great.
Thanks again,
Zoe

-----Original Message-----

I am pulling data from a linked table. I want to display the records
that have 7 consecutive numbers only in a primary key field. The
field has some entries that are 10 numbers; alpha characters mixed in;
alpha only; numerics with spaces in between; dashes between numerics.
To complicate the matter, the linked table has the value set for the
primary key as a text field.

SELECT
*
FROM
yourTable
WHERE
yourTextField Like "*[0-9][0-9][0-9][0-9][0-9][0-9][0- 9]*"

Oops - you said 7 consecutive numbers only. Remove the asterisks on each end!

--
'-------------------------------
' John Mishefske
'-------------------------------

.

 

[John Mishefske]

Thanks John!
I finally got there by using
Like "#######" And Not Like "*a*" And Not Like "*b*" And
Not Like "*-*" And Not Like "*c*" And Not Like "*d*" And
Not Like "*e*" And Not Like "*f*" And Not Like "*g*" And
Not Like "*h*" And Not Like "*i*" And Not Like "*j*" And
Not Like "*k*" And Not Like "*l*" And Not Like "*m*" And
Not Like "*n*" And Not Like "*o*" And Not Like "*p*" And
Not Like "*q*" And Not Like "*r*" And Not Like "*s*" And
Not Like "*t*" And Not Like "*u*" And Not Like "*v*" And
Not Like "*w*" And Not Like "*x*" And Not Like "*y*" And
Not Like "*z*" And Not Like "*" & " " & "*"
Your suggestion is certainly simpler and works great.
Thanks again,
Zoe

You're welcome - good luck on the project.