Extracting similar data from a field

[rheide]

Hello All,
I have a field that contains product information in the following format:
Amount: ||54.97 Qty:1 SKU #:3001 - Ball. Amount: ||24.95 Qty:1 SKU #:1070 - Shirt

There could be one product in the field or more

I'm trying to find a way to extract the sku numbers so I can get it to be something like 3001, 1070

I've searched a lot and found instr and mid, etc. but I can't figure out how to do it if you are trying to pull multiple (but similar) data from one field.

Any suggestions or keywords to search for would be highly appreciated!!

Thanks.

 

[boblarson]

First, the most important question is why do you have data like this?

 

[georgedwilkinson]

Keyword: parse, parser.

Bob's question is still valid. Are you receiving data like this from another source? What do you intend to do with it?

 

[ajetrumpet]

even if u parse this stuff, you will get a headache doing it. there is no easy way to do it the way you have the data. fix it some place else. access doesn't have the tools to make this easy for u

 

[rheide]

Hello All.

Bob - the reason the data is like this is that is how our online shopping cart program writes the order to the orders table in the SQL db. I'm just importing the data from our server's SQL db.

My client is trying to automate some things on their end (in house) so I was just hoping that I could find some query/code that could pull all the sku's ... maybe I'll go back to the server and see if I can pull the data differently - the query I'm pulling now is from the order table created by the code.

Thanks again!