Query Question?

[phimz196]

I have string field in a table the had different spacing.

Examples:

4010252-1-NA-OPT-NA-1-ICA-bal
495390-2-NA-OPT-NA-74-ICAS-bal
490861-1-na-opt-na-4-icas-kn

I am trying to pull all of the numbers after the fifth - (dash)

So the results would be:

1
74
4

Is there any funcation that i can use?

Thanks for the help...

 

[VicRauch]

I would suggest writing a function that accepts this field as input, then do a look looking for the "-". After finding the "-" for the5th time, pass back from the function all the numbers between the 5th and the 6th "-". To find the first occurance, and subsequent ones to, use the function Instr(). For more info on Instr, look it up in Access help.

 

[CT Witter]

If you have access 2000 or better you can use the split() function.

Function Parser(strIn As String, strDelimiter As String, intPosition As Integer)
Dim array1
array1 = Split(strIn, strDelimiter)
Parser = array1(intPosition)
End Function

? Parser("490861-1-na-opt-na-4-icas-kn","-",5)

Use in a query like Parser([FieldName],"-",5)

HTH,
CT

 

[VicRauch]

CT,
Thanks for your posting. I keep forgetting about Split() because I have not used it yet. Keep this up and hopefully I will use it soon instead of my loops.

 

[giacomo]

because split() returns an array you could actually write the function like this:

Public Function Parser(strIn As String, strDelimiter As String, intPosition As Integer)
Parser = Split(strIn, strDelimiter)(intPosition)
End Function

I just think that's kind of neat

 

[Darren Bartrup]

If the bit before your number is always NA-OPT-NA- then you could use the query:

SELECT 
MID([fldCode],InStr([fldCode],"NA-OPT-NA-")+10,(Instr(InStr([fldCode],"NA-OPT-NA-")+10,[fldCode],"-"))-(InStr([fldCode],"NA-OPT-NA-")+9)-1) as Final
FROM Table1;