Extracting data from various positions in a field

[PJname]

Help!


I have a field in a Microsoft Access table where I am trying to pull portions of the data out. Unfortunately, it is not in the same position in this field. No such luck..... or :-(

Anway the following are some examples of what I am trying to pull:

example 1:
DATA IN FIELD DATA TO PULL
N21 -FEEDWTR GLOBAL-040-
Data to pull: N21 -040-

N21 -FEEDWTR GLOBAL-040-A
Data to pull: N21 -040-A

N21 -F014B -020-
Data to pull: N21 -020-

The first part I can pull using the mid function 1,3. This part of the field is standarized.

It is the end of this data where I am having problems since it is not standarized and can be in any position. Most contains a -nnn- I have tried using the InStr with the mid but I can't come up with the correct syntax.

I want to tell it to pull from the right of the field stopping at the second - -040- I think this would work but I am not having much luck.

If someone can send me the MS Access function, I would certainly appreciate it. I need ASAP

Thanks in Advance......

 

[pbrodsky]

maybe something like (haven't tested it)

left(data,4) & mid(5,data,(instr(data,"-")+1),99)

 

[pbrodsky]

sorry - not correct

left(data,4) & mid(data,(instr(data,"-")+1),99) , and I'm never quite sure of the order of the args for instr

 

[pbrodsky]

no - once more

left(data,4) & mid(data,(instr(5,data,"-")+1),99)

also I put the 5, in the wrong place

 

[PHV]

Left([your field],3) & Mid([your field], InStrRev([your field], "-", Len([your field])-2))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PJname]

OK,

I have another challenge. I have a description field where it will contain the words: "Hang Tag" in various locations within this description field

examples:
1C11D001LJ - HCU 44-37 HANG TAG
B21 HANG TAG B21-F022A-003 - AIR AND POWER
F11 CONTINGENCY - HANG TAG F11-E015-005 - HFTS
--------------------------------------------------------
If there is a number which follows "Hang Tag" I want to pull this data into a field and match this number to another table.

--------------------------------------------------------
C11 example: no data
B21 example: B21-F022A-003
F11 example: F11-E015-005


Is there a simple way to do this?

Suggestions will be greatly appreciated.