Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #13106

Re: Splitting a fields data into one or more new recordset(s)

Show all headers | View raw

on 5/6/2018, noodnutt supposed :
> Hi Team
>
> I was hoping someone could assist me with a possible Access equivalent of the 
> ( Excel code at the foot of this post ) in which Excel splits a cell of data, 
> then inserts a new row,  copying certain cell information into the newly 
> created row based on how many orders are included in the target cell. .
>
> Essentially, once orders have been imported via csv file entitled 
> tblImport_Orders, I need it to do the following please.
>
> From this:
> txfLoadID.....txfPONo.....................................txfVendor
> 3006119.......0057536485,0057536571,0057536709,0057550314.XYZ Sales
>
> To This: ( removing the leading zeros ).
> txfLoadID.....txfPONo......txfVendor
> 3006119.......57536485.....XYZ Sales
> 3006119.......57536571.....XYZ Sales
> 3006119.......57536709.....XYZ Sales
> 3006119.......57550314.....XYZ Sales
>
> In total their is 14 fields:
> txfLoadID*
> txfPONo*
> txfVendorID*
> txfVendor*
> txfSuburb*
> txfDest*
> snfStacks
> snfWeight
> snfWoods
> txfRoute*
> txfDriver*
> txfLoad*
> dtfVenArr*
> dtfDCArr*
>
> Of the 14 fields above, only 11* of the fields need to be replicated and 
> copied into the new recordset(s), if! there is more than one PONo attached to 
> a LoadID. The overall number of newly generated recordset(s) will be 
> determined by how many PONos are in the target field ( txfPONo ).
>
> The (single ) number digit fields do not require replicating as they will be 
> populated via another process that looks at the individual PONo(s) volume as 
> opposed to the entire LoadId volume which is the cumulative amount. I will be 
> looking to use a Lookup based on the LoadID and the individual PONo ( which 
> is provided in a different import file from our customers Planning Team ) for 
> this step.
>
> The company wants to transition away from individual Excel files ( one for 
> every day of operation, totally thousands of individual files ), into a 
> single container. And in as much as utilising the existing excel process as 
> the springboard for converting these orders would be better, then importing 
> the conversions, they ( and I suppose, I ) would like to explore the option 
> of doing it within the same environment, if possible that is!
>
> Fairly tall order for me, and i'm guessing many others, but! I am hopeful 
> someone can point me in the right direction.
>
> Kind regards
> TIA
> Mark
>
> Excel Splitting code:
>
> Sub SplitInbounds()
>
> For col = 1 To 14
>
> Select Case Sheets("Inbound").Cells(4, col).Value
> Case "Driver"
> Drv = col
> Case "Stacks"
> pltSpace = col
> Case "Woods"
> woods = col
> Case "Weight"
> wght = col
> End Select
>
> Next col
>
> st = 5
> For i = 5 To 350
>
> cDrv = Cells(i, "I").Value
>
> If cDrv <> Cells(i + 1, "I").Value Then
>
> Range(Cells(i + 1, 1), Cells(i + 1, getUsedColumns)).Select
> Selection.Insert Shift:=xlDown
> Selection.Interior.ColorIndex = 37
> Selection.Font.Bold = True
>
> plts = plts + Cells(i, "E").Value
> wds = wds + Cells(i, "F").Value
> wg = wg + Cells(i, "H").Value
>
> Cells(i + 1, "E") = plts
> Cells(i + 1, "F").Value = wds
> Cells(i + 1, "H").Value = wg
>
> st = i + 1
> en = i + 1
> plts = 0
> wds = 0
> wg = 0
>
> i = i + 1
>
> ElseIf cDrv = Cells(i, "I").Value Then
>
> plts = plts + Cells(i, "E").Value
> wds = wds + Cells(i, "F").Value
> wg = wg + Cells(i, "H").Value
>
> End If
>
> Next i
>
> End Sub
>
>
>
> ---
> This email has been checked for viruses by AVG.
> http://www.avg.com

I am not sure I understand what you are trying to do, but it looks like 
you have a string delimited by Periods, and Comma's  I that's the case 
I think this code will do what you want.

Public Sub Test()
  Dim i As Integer
  Dim s As String
  Dim a() As String, potxt() As String

  s = "3006119.......0057536485,0057536571,0057536709,0057550314.XYZ 
Sales"
    a = Split(s, ".")
    potxt = Split(a(7), ",")
    For i = 0 To UBound(potxt)
    Debug.Print a(0); ", "; a(1); ", "; a(2); ", "; a(3); ", "; a(4); 
", "; a(5); ", "; a(6); ", "; potxt(i); ", "; a(8)
    Next
End Sub

Here are the results I get from the Immediate window

call test
3006119, , , , , , , 0057536485, XYZ Sales
3006119, , , , , , , 0057536571, XYZ Sales
3006119, , , , , , , 0057536709, XYZ Sales
3006119, , , , , , , 0057550314, XYZ Sales

Am I close?

Rdub

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-05 23:11 -0700
  Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-05 23:50 -0700
  Re: Splitting a fields data into one or more new recordset(s) Patrick Finucane <patrickfinucanetx@gmail.com> - 2018-05-06 06:32 -0700
  Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-06 10:19 -0400
  Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-07 05:38 -0700
    Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-07 10:20 -0400
      Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-07 10:41 -0400
        Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-08 05:26 -0700
          Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-08 10:04 -0400
            Re: Splitting a fields data into one or more new recordset(s) Ron Paii <ron81pai@gmail.com> - 2018-05-08 10:57 -0700
              Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-10 05:55 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Paii <ron81pai@gmail.com> - 2018-05-10 06:58 -0700
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-11 05:54 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Paii <ron81pai@gmail.com> - 2018-05-11 10:57 -0700
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-11 19:49 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Paii <ron81pai@gmail.com> - 2018-05-14 05:17 -0700
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-18 05:35 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-10 09:58 -0400
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-11 06:17 -0700
                Re: Splitting a fields data into one or more new recordset(s) Patrick Finucane <patrickfinucanetx@gmail.com> - 2018-05-11 06:40 -0700
                Re: Splitting a fields data into one or more new recordset(s) Ron Weiner <rw@domain.com> - 2018-05-11 15:26 -0400
                Re: Splitting a fields data into one or more new recordset(s) noodnutt <noodnutt@gmail.com> - 2018-05-11 19:51 -0700

csiph-web