How to create a temp table from concatenated fields that vary in length (1 Viewer)

[BenMason]

I have a parts table. In it there are two fields that have string info (Customer and CPN). For instance:

Parts Table Example:

PartNo	Customer	CPN
1678	Yen/Stick/Pearson	Yen: 2321*Stick: 52344J/45*Pearson: Hy45
1679	Carson	Cy8911
1680
1681	Harry's

In the example above, there are three customers that use the partNo 1678 separated by “/”, and the CPN is separated by an asterisk “*”

I want to create a temp table that looks like this:

PartNo	Customer	CPN
1678	Yen	2321
1678	Stick	52344J/45
1678	Pearson	Hy45

I tried strVarCust1 = InStr(1, [strPartCustomer1], "/") to count the position before the first slash in vba with not much luck because the Parts table can have a customer field that has one customer, or three or more customers or it can be blank. The same with the CPN field.

I think I need a way to determine how many “/” or customers are in the Customer field and how many CPN’s are in the CPN field. Then write it to a string variable and then write the string variable to the temp table. But not sure how to do this or if this would be the correct solution.

There must be an easy way to do this.

Please help.

Thanks,

Ben

 

[theDBguy]

Hi Ben. You should try the Split() function.

 

[arnelgp]

Public Function fnSplit(ByVal pString As String, ByVal pDelim As String, ByVal pItemNumber As Integer) As String
    Dim v As Variant
    If pItemNumber < 1 Then
        Exit Function
    End If
    v = Split(pString, pDelim)
    If UBound(v) + 1 >= pItemNumber Then
        fnSplit = Trim$(v(pItemNumber - 1))
    End If
End Function

 

[Cronk]

The table Parts Table Example is not normalized and should be the temporary table.

 

[BenMason]

Public Function fnSplit(ByVal pString As String, ByVal pDelim As String, ByVal pItemNumber As Integer) As String
    Dim v As Variant
    If pItemNumber < 1 Then
        Exit Function
    End If
    v = Split(pString, pDelim)
    If UBound(v) + 1 >= pItemNumber Then
        fnSplit = Trim$(v(pItemNumber - 1))
    End If
End Function

Thanks for the function. I copied it to my functions module. But then I am lost. I tried to create a query using it and received #error in the field. Here's the query: MyField1: fnSplit(1,[Customer],"/")

Ben

 

[BenMason]

Hi Ben. You should try the Split() function.

Thanks. I'm looking up the split() function.

Ben

 

[theDBguy]

Thanks. I'm looking up the split() function.

Ben

Hi Ben. I'm sure Arnel will be able to help you with using it, as he showed in his sample function. Good luck!

 

[BenMason]

The table Parts Table Example is not normalized and should be the temporary table.

You are right. The Parts table is not normalized. I'm trying to create a temp table but need to split the customer and PN and then write to the temp table. The splitting is where I am stopped. In the meantime I am creating a normalized CPN table that has the splits but it's very manual. Then I will have to find a way for the users to enter their updates directly in the new CPN table instead of concatenated as they are doing now.

 

[Gasman]

Thanks for the function. I copied it to my functions module. But then I am lost. I tried to create a query using it and received #error in the field. Here's the query: MyField1: fnSplit(1,[Customer],"/")

Ben

You need to pay attention to the parameters that arnelgp's function expects?

 

[arnelgp]

MyField1: fnSplit([Customer],"/",1)

 

[Pat Hartman]

If you want to end up with one row in and n rows out, you are going to have to do this with a VBA code loop. Open a recordset that reqds the table in question. In the loop, split the two fields. Then create an inner loop that writes one row for each value in the first array. You can assume the two arrays have the same number of items so item 3 in the first array uses the same index value as the seond array.