Search for string anywhere in string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a long list of partial numbers all of which start with "W" and then
have 4 numbers after it with the 4 numbers being any combination of numbers.
The actual part number starts with any number of characters, but the majority
have mostly 3-5 leading numbers. After the "Wxxxx" there can be numerous
numbers and/or letters. I have all of the "W" numbers in their own table in
the database. I want to find all the part numbers that has "Wxxxx" within
the part number. How would I go about doing this?

Example of a part number: 245W1234890123 or BAAZ3G011W3022NN

Let me know if you need any other info. Thanks for your help.
 
I have a long list of partial numbers all of which start with "W" and then
have 4 numbers after it with the 4 numbers being any combination of numbers.
The actual part number starts with any number of characters, but the majority
have mostly 3-5 leading numbers. After the "Wxxxx" there can be numerous
numbers and/or letters. I have all of the "W" numbers in their own table in
the database. I want to find all the part numbers that has "Wxxxx" within
the part number. How would I go about doing this?

Example of a part number: 245W1234890123 or BAAZ3G011W3022NN

Let me know if you need any other info. Thanks for your help.
Click to expand...

So, in the first example you wish to return 1234890123 and in the
second example, 3022 ?

Add a new column to your query.

NumberOnly:Val(Mid([FieldName],InStr([FieldName],"W")+1))
 
Use criteria like the following to find all records wtihere the Part number
contains the letter W followed by 4 number characters followed by anything
(including nothing).

Field: YourField
Criteria: LIKE "*W[0-9][0-9][0-9][0-9]*"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
The criteria works nicely below but . . . What if I want to isolate the "W"
plus 4 numbers? In other words I want to pull out that combo from the part
number and then compare the results to a long list of "W" numbers that was
sent to me. I realized after I posted that I did not explain the problem
thoroughly enough.

Example of a part number: 245W1234890123 or BAAZ3G011W3022NN

I want to see only "W1234" and "W3022" and then run another table that has
only some "W" numbers in it against this new list of numbers.

John Spencer said:
Use criteria like the following to find all records wtihere the Part number
contains the letter W followed by 4 number characters followed by anything
(including nothing).

Field: YourField
Criteria: LIKE "*W[0-9][0-9][0-9][0-9]*"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Walter said:
I have a long list of partial numbers all of which start with "W" and then
have 4 numbers after it with the 4 numbers being any combination of
numbers.
The actual part number starts with any number of characters, but the
majority
have mostly 3-5 leading numbers. After the "Wxxxx" there can be numerous
numbers and/or letters. I have all of the "W" numbers in their own table
in
the database. I want to find all the part numbers that has "Wxxxx" within
the part number. How would I go about doing this?

Example of a part number: 245W1234890123 or BAAZ3G011W3022NN

Let me know if you need any other info. Thanks for your help.
Click to expand...
Click to expand...
 
Okay, I figured it out. I did some data manipulation in Excel and then
dropped the data back into an Access table and linked my other table to it
and got the results that I wanted. No need to answer my latter question.

Walter said:
The criteria works nicely below but . . . What if I want to isolate the "W"
plus 4 numbers? In other words I want to pull out that combo from the part
number and then compare the results to a long list of "W" numbers that was
sent to me. I realized after I posted that I did not explain the problem
thoroughly enough.

Example of a part number: 245W1234890123 or BAAZ3G011W3022NN

I want to see only "W1234" and "W3022" and then run another table that has
only some "W" numbers in it against this new list of numbers.

John Spencer said:
Use criteria like the following to find all records wtihere the Part number
contains the letter W followed by 4 number characters followed by anything
(including nothing).

Field: YourField
Criteria: LIKE "*W[0-9][0-9][0-9][0-9]*"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Walter said:
I have a long list of partial numbers all of which start with "W" and then
have 4 numbers after it with the 4 numbers being any combination of
numbers.
The actual part number starts with any number of characters, but the
majority
have mostly 3-5 leading numbers. After the "Wxxxx" there can be numerous
numbers and/or letters. I have all of the "W" numbers in their own table
in
the database. I want to find all the part numbers that has "Wxxxx" within
the part number. How would I go about doing this?

Example of a part number: 245W1234890123 or BAAZ3G011W3022NN

Let me know if you need any other info. Thanks for your help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top