Extracting a value from a string

[Anthony Viscomi]

My new client has a Access db which contians inventory data. It has a
Description field which contains the Vendors' part# along with some other
text. It looks as if 90% of the part#'s always start with the "#" sign and
end with a space; followed by text. I would like to be able to extract the
part# from this field and palce it within new field that I will create
within the same table. Is there a method a performing this task? How?

Thanks in advance!
Anthony Viscomi

 

[Allen Browne]

Use a query to extract the part number from the text:

1. Use Instr() to locate the # sign in the text, by typing something like
this into a fresh column in the Field row:
StartsAt: Instr([Field1], "#")
and in the Criteria under this:

0

2. In the next column, locate the space that ends the part number:
EndsAt: Instr([StartsAt], [Field1], " ")
with Criteria:

0

3. In the next column:
Trim(Mid([Field1], [StartsAt]+1, EndsAt - [StartsAt] - 2))

4. Test. Once you see the correct results, change the query into an Update
query (Update on query menu), and write the results to your new part number
field.

 

[Larry Linson]

The InStr function returns the position of a specified string within another
string. You can use it to find the "#" character, and then, from that point,
use it again to find the next " " character. With those values, you have
enough information to use the Mid function to obtain the string. Better
check them manually, after you extract them, because there could be
something other than a part number that begins with "#" and ends with " ".
If the part number is the last thing in the description, it may not end with
" " -- could end with "" (null string) or "." (period), for example. So if
you find "#", you may want to check for those, as well as " ".

Larry Linson
Microsoft Access MVP

 

[Anthony Viscomi]

Thanks all!

The InStr function returns the position of a specified string within
another
string. You can use it to find the "#" character, and then, from that
point,
use it again to find the next " " character. With those values, you have
enough information to use the Mid function to obtain the string. Better
check them manually, after you extract them, because there could be
something other than a part number that begins with "#" and ends with " ".
If the part number is the last thing in the description, it may not end
with
" " -- could end with "" (null string) or "." (period), for example. So if
you find "#", you may want to check for those, as well as " ".

Larry Linson
Microsoft Access MVP