Break out a part number into seperate fields 3

[03Explorer]

I have part numbers like this: 5-6-2-3-1-1

I want each numeric value to be populating a column of its own

first     second     third     fourth    fifth     sixth
=====     ======     =====     ======    =====     =====
5         6          2         3         1         1

However, the catch is there could be double or triple digits between dashes
5-67-2-3-1-1
5-6-2-231-1-2

first     second     third     fourth    fifth     sixth
=====     ======     =====     ======    =====     =====
5         67          2         3         1         1
5         6           2         231       1         1

What is the best query method to break these out of the one string value?

 

[SkipVought]

Hi,

This doesn’t look like a query problem.

I’d import into Excel and use the Data > Text to columns... feature, DELIMIT on a DASH.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[03Explorer]

=\ I need the query to take values and populate another table that has a field per numeric value so when we report on the categories they are populated into foreign key fields. No Excel in this equation.

 

[SkipVought]

Are there ALWAYS 6 numbers?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[03Explorer]

Yes, that is the system.

 

[SkipVought]

SELECT 
  mid([Part Number],1,instr([Part Number],'-')-1)
, MID([Part Number],instr([Part Number],'-')+1,instr(instr([Part Number],'-')+1,[Part Number],'-')-instr([Part Number],'-')-1)
, MID([Part Number],
instr(instr([Part Number],'-')+1,[Part Number],'-')+1,
instr(instr(instr([Part Number],'-')+1,[Part Number],'-')+1,[Part Number],'-')-
instr(instr([Part Number],'-')+1,[Part Number],'-')-1)
, MID([Part Number],
instr(instr(instr([Part Number],'-')+1,[Part Number],'-')+1,[Part Number],'-')+1,
instr(instr(instr(instr([Part Number],'-')+1,[Part Number],'-')+1,[Part Number],'-')+1,[Part Number],'-')-
instr(instr(instr([Part Number],'-')+1,[Part Number],'-')+1,[Part Number],'-')-1)
, MID([Part Number],
instr(instr(instr(instr([Part Number],'-')+1,[Part Number],'-')+1,[Part Number],'-')+1,[Part Number],'-')+1,
instr(instr(instr(instr(instr([Part Number],'-')+1,[Part Number],'-')+1,[Part Number],'-')+1,[Part Number],'-')+1,[Part Number],'-')-
instr(instr(instr(instr([Part Number],'-')+1,[Part Number],'-')+1,[Part Number],'-')+1,[Part Number],'-')-1)
, MID([Part Number],
instr(instr(instr(instr(instr([Part Number],'-')+1,[Part Number],'-')+1,[Part Number],'-')+1,[Part Number],'-')+1,[Part Number],'-')+1,
999)

FROM [Your Table]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

I would create a small user-defined function that can be used to return any section of text like this with any delimiter. You could call this like:

ParseText([Part Number],4,"-")
The "4" returns the 4th section.

Public Function ParseText(pstrText As String, intElement As Integer, _
        pstrDelimiter As String) As Variant
    Dim arText() As String
   On Error GoTo ParseText_Error

    arText() = Split(pstrText, pstrDelimiter)
    ParseText = arText(intElement - 1)
    
ExitParseText:
   On Error GoTo 0
   Exit Function

ParseText_Error:
    Select Case Err
        Case 9 'subscript out of range
            'don't do anything
        Case Else
            MsgBox "Error " & Err.Number & " (" & _
                Err.Description & ") in procedure ParseText of Module basParseText"
    End Select
    Resume ExitParseText
End Function

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[03Explorer]

Duane, I appreciate your concept and code. Skip, I believe your code is in raw great, but I lean towards the simple concept of having a function for the Rinse-and-Repeat concept of reusing code... Keeping it simple.

Thank you both for your time and efforts in answering my question!

Rob
Minneapolis also(Duane)

 

[SkipVought]

I’d go with Duane’s function, too!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]