Trying to split a dashed number into multiple columns (1 Viewer)

klesauski · Jul 22, 2013

Hi, I am going to start off saying I am very new to Access so that is probably why this is so difficult for me.

I am trying to split a number sequence (Ex. 25372-400-E10-0000-D0021) Where the third number in the sequence can be either 2 or 3 numbers/letters.

The idea is to have a query that will split this number into its individual parts. I have seen several forums about splitting two numbers and such but I can't seem to make that work when I need to create 5 columns from this one field.

Thanks in advance for your help. If I missed this thread somewhere I also wouldn't mind you sending me there if this question has already been answered.

pr2-eugin · Jul 22, 2013

Hello klesauski.. Welcome to AWF..

You can use the Split function to split the different parts.. However you might need a custom function to do this, as the requirement is to use in a Query..

JohnLee · Jul 22, 2013

Hi,

I'm assuming that you have a table that contains your sequence number.

I've attached my example db which is Access 2000 version.

I've created an example table with an autoID field and a text data field in it, and a basic query.

If you open the query up in design view you will see how I have answered your question. If you run the query you will see the correct results for your scenario.

I've used your example sequence number to demonstrate the results you are looking for.

I hope this is of assistance to you

JOhn

nanscombe · Jul 22, 2013

. and if you want to split it up with a bit of code.

Code:

Public Sub testSpitCode5()
' [COLOR="red"]This is a bit of demo code to show splitCode5() in action[/COLOR]
Dim part1 As String, part2 As String, part3 As String, part4 As String, part5 As String

' [COLOR="red"]Pass the Code and 5 empty string variables to hold the results[/COLOR]
  splitCode5 "25372-400-E10-0000-D0021", part1, part2, part3, part4, part5

' [COLOR="red"]Print the results to the immediate window[/COLOR]
  Debug.Print part1, part2, part3, part4, part5

End Sub

Public Sub splitCode5(ByVal sp5Code As String, ByRef sp5Part1 As String,  ByRef sp5Part2 As String, ByRef sp5Part3 As String, ByRef sp5Part4 As String, ByRef sp5Part5 As String)
' [COLOR="Red"]sp5Code - Is the string passed as a value[/COLOR]
' [COLOR="red"]sp5Part1-sp5Part5 are string variables passed by reference to get the data out[/COLOR]

' [COLOR="red"]Defines an array[/COLOR]
  Dim Code5() As String

' [COLOR="red"]Splits sp5Code into an array of 5 parts using the '-'[/COLOR] 
  Code5 = Split(sp5Code, "-")

' [COLOR="red"]Passes back the data via the referenced variables[/COLOR]
  sp5Part1 = Code5(0)
  sp5Part2 = Code5(1)
  sp5Part3 = Code5(2)
  sp5Part4 = Code5(3)
  sp5Part5 = Code5(4)

End Sub

SOS · Jul 22, 2013

nanscombe said:

. and if you want to split it up with a bit of code.

Code:

Public Sub testSpitCode5()
' [COLOR=red]This is a bit of demo code to show splitCode5() in action[/COLOR]
Dim part1 As String, part2 As String, part3 As String, part4 As String, part5 As String
 
' [COLOR=red]Pass the Code and 5 empty string variables to hold the results[/COLOR]
  splitCode5 "25372-400-E10-0000-D0021", part1, part2, part3, part4, part5
 
' [COLOR=red]Print the results to the immediate window[/COLOR]
  Debug.Print part1, part2, part3, part4, part5
 
End Sub
 
Public Sub splitCode5(ByVal sp5Code As String, ByRef sp5Part1 As String,  ByRef sp5Part2 As String, ByRef sp5Part3 As String, ByRef sp5Part4 As String, ByRef sp5Part5 As String)
' [COLOR=red]sp5Code - Is the string passed as a value[/COLOR]
' [COLOR=red]sp5Part1-sp5Part5 are string variables passed by reference to get the data out[/COLOR]
 
' [COLOR=red]Defines an array[/COLOR]
  Dim Code5() As String
 
' [COLOR=red]Splits sp5Code into an array of 5 parts using the '-'[/COLOR] 
  Code5 = Split(sp5Code, "-")
 
' [COLOR=red]Passes back the data via the referenced variables[/COLOR]
  sp5Part1 = Code5(0)
  sp5Part2 = Code5(1)
  sp5Part3 = Code5(2)
  sp5Part4 = Code5(3)
  sp5Part5 = Code5(4)
 
End Sub

Interesting way to get the output from a single function call. Never seen that done before like that. But my normal way is to make it more reusable so that it matters not how many parts you have. If you pass it 4 or 30, it would still work.

Code:

Public Function SplitVal(strIn As String, strDelimiter As String, lngItem As Long) As String
    Dim varSplit As Variant
    varSplit = Split(strIn, strDelimiter)
    If lngItem <= UBound(varSplit) Then
        SplitVal = varSplit(lngItem)
    Else
        SplitVal = "Item Number not valid"
    End If
End Function

And then for each field in the query

Select SplitVal([FieldNameHere], "-", 0) As FirstField, SplitVal([FieldNameHere], "-", 1) As SecondField, etc....

And the only reason why I include the delimiter is that you can use this for other things as well.

nanscombe · Jul 23, 2013

I have also done similar tasks in the same manner as you describe in the past as well.

For pure reasons of discussion ...

Sometimes I might throw in a Static variable so I didn't have to perform part of the function every time.

Code:

Public Function SplitVal(ByVal strIn As String, ByVal strDelimiter As String, ByVal lngItem As Long) As String
    [COLOR="Red"]Static s_strIn as String, varSplit As Variant[/COLOR]

    [COLOR="red"]If s_strIn <> strIn Then
      varSplit = Split(strIn, strDelimiter)
      s_strIn = strIn
    Endif[/COLOR]

    If lngItem <= UBound(varSplit) Then
        SplitVal = varSplit(lngItem)
    Else
        SplitVal = "Item Number not valid"
    End If
End Function

Or even move parameters around so I could make them optional.

Code:

Public Function SplitVal(ByVal lngItem As Long, Optional byVal strIn As String = "", Optional byVal strDelimiter As String="") As String
    [COLOR="Red"]Static varSplit As Variant[/COLOR]

    [COLOR="red"]If strIn <> "" Then varSplit = Split(strIn, strDelimiter)[/COLOR]

    If lngItem <= UBound(varSplit) Then
        SplitVal = varSplit(lngItem)
    Else
        SplitVal = "Item Number not valid"
    End If
End Function

First call SplitVal(0, "25372-400-E10-0000-D0021","-")
Second call SplitVal(1)
Third call SplitVal(2)
...

ChrisO · Jul 23, 2013

I think that if it is going to be called from a Query the code should at least be able to handle a Null being passed to it:-

Code:

Public Function SplitValue(ByVal Value As Variant, _
                           ByVal Index As Long) As Variant

    If Len(Value) Then
        SplitValue = Split(Value, "-")(Index - 1)
    End If

End Function

Call from Query as:-
1st Column: SplitValue([strNumber],1)
2nd Column: SplitValue([strNumber],2)
and so forth.

Using the sample in post #3, a demo is attached.

Chris.

nanscombe · Jul 23, 2013

Now that's an interesting way to use the Split() function.

Trying to split a dashed number into multiple columns (1 Viewer)

klesauski

New member

pr2-eugin

Super Moderator

JohnLee

Registered User.

Attachments

nanscombe

Registered User.

SOS

Registered Lunatic

nanscombe

Registered User.

ChrisO

Registered User.

Attachments

nanscombe

Registered User.

Similar threads

Users who are viewing this thread