Query to pull out sales order number

[lucy6868]

In my UPS Worldship database, my shipping guy sometimes types in the sales order number like this:

20043818 / 30108 - 1

and sometimes like this:

15993 - 20 Hoods / 20043700

The sales order number always starts with 200 and is 8 digits long.
I am trying to write a select query that only pulls out the sales order number so I can link it to the sales order table.

Any help would be greatly appreciated.

Stephanie

 

[accessfever]

You may want to create a query to find out where the "200" is in that field by MID function. I would create a number of fields based on the lenght of that order field minus 7 plus 1 . E.g. If the order field is 21 characters long, then create a query with 15 fields (21-7 +1=15):
Field1: iff (MID([orderfield],1,3]="200",MID([orderfield],1,8,) ,"")
Field2: Iff (MID([orderfield],2,3]="200",MID([orderfield],2,8),"")
"
Field 14: Iff (MID([orderfield],14,3]="200",MID([orderfield],14,8),"")
Field 15: trim([Field1]) & trim([Field2]) & ...... & trim([Field14])

Hope this helps.

 

[accessfever]

I forgot to mention this:

the last field (in my example is FIELD 15) should have the order number.