Extracting numeric vs text data

[Guest]

I have a field that has two differernt kinds of data input

One is numeric all number e.g 1214-1452-215 and 2154-4152-220

and the other is part numeric, part text e.g. CASE-BOOK-220 and BASE-MAKE-230.

If I want to select extract in a query just those data which are part
numeric and part text which syntax should I use.

Alylia

 

[Van T. Dinh]

.... WHERE [YourField] Like "[A-Z]*[0-9]*"

 

[John Spencer]

Use criteria similar to

LIKE "*[A-Z]*"

That gets all records that have a letter in them. If you need letters and
numbers then
LIKE "*[A-Z]*" AND LIKE "*#*"

If it is always letters followed by numbers; or numbers, letters, numbers
then
LIKE "*[A-Z]*#*"

 

[Guest]

Thanks a lot for your prompt support.

I have truncated the middle characters in this field into another field in
the query ( Right(Left([YourTable].[YourField],9),4)), but I cannot query on
this new field. example, If I want all records with 1452 to be extracted from
the query I get the message "Data type mismatch in criteria expression" .

Please help me out.

.... WHERE [YourField] Like "[A-Z]*[0-9]*"

--
HTH
Van T. Dinh
MVP (Access)

I have a field that has two differernt kinds of data input

One is numeric all number e.g 1214-1452-215 and 2154-4152-220

and the other is part numeric, part text e.g. CASE-BOOK-220 and
BASE-MAKE-230.

If I want to select extract in a query just those data which are part
numeric and part text which syntax should I use.

Alylia

 

[Guest]

I have truncated already into a field. What I intend to do is to put [Enter
Fieldname] under criteria and then if I click on run I should be able to
enter the criterion on which data should be extracted in the message box that
comes up.

Use criteria similar to

LIKE "*[A-Z]*"

That gets all records that have a letter in them. If you need letters and
numbers then
LIKE "*[A-Z]*" AND LIKE "*#*"

If it is always letters followed by numbers; or numbers, letters, numbers
then
LIKE "*[A-Z]*#*"

I have a field that has two differernt kinds of data input

One is numeric all number e.g 1214-1452-215 and 2154-4152-220

and the other is part numeric, part text e.g. CASE-BOOK-220 and
BASE-MAKE-230.

If I want to select extract in a query just those data which are part
numeric and part text which syntax should I use.

Alylia

 

[Van T. Dinh]

Without seeing your data or your Query / SQL String, my guess is that it is
Text value and you should use "1452" (including the double quotes) in your
Query rather than 1452 which is a numeric value.