query for specific data configuration

  • Thread starter Thread starter Zoe Norleen
  • Start date Start date
Z

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
 
Zoe said:
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.
Click to expand...

SELECT
*
FROM
yourTable
WHERE
yourTextField Like "*[0-9][0-9][0-9][0-9][0-9][0-9][0-9]*"
 
John said:
Zoe said:
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.
Click to expand...


SELECT
*
FROM
yourTable
WHERE
yourTextField Like "*[0-9][0-9][0-9][0-9][0-9][0-9][0-9]*"
Click to expand...

Oops - you said 7 consecutive numbers only. Remove the asterisks on each end!
 
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-----
John said:
Zoe said:
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.
Click to expand...


SELECT
*
FROM
yourTable
WHERE
yourTextField Like "*[0-9][0-9][0-9][0-9][0-9][0-9][0- 9]*"
Click to expand...

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

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

.
Click to expand...
 
Zoe said:
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
Click to expand...

You're welcome - good luck on the project.
 
Back
Top