Extracting and Parsing data

[Guest]

I have a field in a table that has information combined.
Example: red 12345

I need to join the table for a query based on the item number which is
12345. How can I parse the data and create a table I can join.

Any Idea's and thanks in advance for you help!

 

[Guest]

Several issues here, but if the field [origname] ALWAYS
has the form "something/single space/itemnumber", and the
itemnumber is ALWAYS a numeric string, and you have to
join to a numeric field in the other table, then you
should first make a query that includes the calculated
variable ItemNumber as follows:

ItemNumber: val(Right([origname],Len([origname])-InStr
([origname]," ")))

You can then use this query to join to the other table via
ItemNumber. If the joining field is a text field, then
you can leave the val() outer wrapper off.

As mentioned, there are other issues (e.g. what is the
destination of the output of the query that joins with the
other table?; is it desireable to restructure the original
table so the item number portion of origname is always in
the record?, etc.), but ths should help for openers.

Hope this helps.