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

Groups > comp.databases.ms-access > #13103 > unrolled thread

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

Back to article view | Back to comp.databases.ms-access

Contents

  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

Page 1 of 2  [1] 2  Next page →

#13103 — Splitting a fields data into one or more new recordset(s)

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

[toc] | [next] | [standalone]

#13104

Oops!

My bad people, I inserted the wrong Excel code. It is the following:

For j = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If InStr(Range("B" & j).Value, ",") > 0 Then
        varSplit = Split(Range("B" & j).Value, ",")
        Range("A" & j + 1).Resize(UBound(varSplit), 14).Insert shift:=xlDown
        Range("B" & j).Resize(UBound(varSplit) + 1, 1).Value = Application.Transpose(varSplit)
        Range("A" & j).Resize(UBound(varSplit) + 1, 1).Value = Range("A" & j).Value
        Range("C" & j).Resize(UBound(varSplit) + 1, 1).Value = Range("C" & j).Value
        Range("D" & j).Resize(UBound(varSplit) + 1, 1).Value = Range("D" & j).Value
        Range("E" & j).Resize(UBound(varSplit) + 1, 1).Value = Range("E" & j).Value
        Range("F" & j).Resize(UBound(varSplit) + 1, 1).Value = Range("F" & j).Value
        Range("J" & j).Resize(UBound(varSplit) + 1, 1).Value = Range("J" & j).Value
        Range("L" & j).Resize(UBound(varSplit) + 1, 1).Value = Range("L" & j).Value
    End If
Next j

[toc] | [prev] | [next] | [standalone]

#13105

On Sunday, May 6, 2018 at 2:11:30 AM UTC-4, noodnutt wrote:
> 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

Here's some code to get you started
Public Sub E()
  Dim intPeriod As Integer
  Dim s As String
   
  s = "3006119.......0057536485,0057536571,0057536709,0057550314.XYZ Sales"
  intPeriod = InStrRev(s, ".")    'get last period
  Debug.Print Mid(s, intPeriod + 1) 'prints XYZ Sales
  s = Left(s, intPeriod - 1)
  intPeriod = InStrRev(s, ".")  'last period before orders
   Debug.Print UBound(Split(s, ",")) + 1  '# of recorods to create
  
     
End Sub

This prints out  XYZ Sales and the number 4 for the number of records to create.

[toc] | [prev] | [next] | [standalone]

#13106

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

[toc] | [prev] | [next] | [standalone]

#13107

Hi Patrick & Ron

I appreciate your responses. I think I need to go into some deeper explanation as to what I need v. if possible.

tblImport_Orders is purely for importing the csv file which could contain up to 250 rows of data, and each row can contain PO Number or multiples. ( Ron, you are correct in the comma being the delimiter ).

Patrick, I think (s) would need to be a variable in that it would not be the same string length each time.

I think I may have confused both of you with the period separating the customer name. I merely did that to try and keep registration. The actual customer name along with the other 10 fields need to be copied to the new record(s) in their own respective fields.

So, I was thinking ( and this is nowhere what the syntax should be, but it's the best way I can explain it, I hope.. ).

Before continuing, tblImport_Orders data will append it's data to tblOrders_Inbound and then data will be deleted after this process, this will be the Rinse & Repeat each day.

So, something like this:

For each Record in tblImport_Orders
 If txfPONo has more than one PO then
   With txfPONo  
    Split each PONo into new Record
    Copy Fields( 1,3,4,5,6,10,11,12,13,14 ) from this record to new record(s)
   End With
 Else: Exit Sub
Next Record

Thanks again for your time guys, very much appreciated.

Cheers
Mark.

[toc] | [prev] | [next] | [standalone]

#13108

on 5/7/2018, noodnutt supposed :
> Hi Patrick & Ron
>
> I appreciate your responses. I think I need to go into some deeper 
> explanation as to what I need v. if possible.
>
> tblImport_Orders is purely for importing the csv file which could contain up 
> to 250 rows of data, and each row can contain PO Number or multiples. ( Ron, 
> you are correct in the comma being the delimiter ).
>
> Patrick, I think (s) would need to be a variable in that it would not be the 
> same string length each time.
>
> I think I may have confused both of you with the period separating the 
> customer name. I merely did that to try and keep registration. The actual 
> customer name along with the other 10 fields need to be copied to the new 
> record(s) in their own respective fields.
>
> So, I was thinking ( and this is nowhere what the syntax should be, but it's 
> the best way I can explain it, I hope.. ).
>
> Before continuing, tblImport_Orders data will append it's data to 
> tblOrders_Inbound and then data will be deleted after this process, this will 
> be the Rinse & Repeat each day.
>
> So, something like this:
>
> For each Record in tblImport_Orders
>  If txfPONo has more than one PO then
>    With txfPONo  
>     Split each PONo into new Record
>     Copy Fields( 1,3,4,5,6,10,11,12,13,14 ) from this record to new record(s)
>    End With
>  Else: Exit Sub
> Next Record
>
> Thanks again for your time guys, very much appreciated.
>
> Cheers
> Mark.
>

Completely untested air code, but this should give you the general idea 
of how I would attack this sort of process.  The only weird thing that 
many Access programmers will see here is the use of ADODB recordset 
instead of the more common DAO type.  I have been using ADODB for soe 
long in so many different programming environments that i long ago 
decided to just "stick" with it for all projects.  Anyway here is 
something to get you started with.

Public Sub MoveFromImportToImbound()

    Dim rsImport As ADODB.Recordset
    Dim i As Integer
    Dim POs() As String, strSql As String

    Set rsImport = New ADODB.Recordset
    rsImport.Open "tblImport_Orders ", CurrentProject.Connection, 
adOpenDynamic, adLockPessimistic, adCmdTable
    With rsImport
        Do While Not .EOF
            POs = Split(!FieldPOs, ",")
            For i = 0 To UBound(POs)
                ' We are creating a Sql Insert statement here that we 
will execute for each record we move
                ' I am assuming all fields are text as wrapping them in 
single quotes
                ' and doubling any Single quotes found in the data
                ' Another way to do this would be to  open another 
recordset to the tblImport_Orders
                ' table and AddNew for each record.
                strSql = "Insert Into tblImport_Orders 
(fld1,fld3,fld4,fld5,fld6,fieldPOs,fld11,fld12,fld13,fld14 ) " _
                    & "Values('" & Replace(!fld1, "'", "''") & "', '" _
                    & Replace(!fld1, "'", "''") & "', '" _
                    & Replace(!fld3, "'", "''") & "', '" _
                    & Replace(!fld4, "'", "''") & "', '" _
                    & Replace(!fld5, "'", "''") & "', '" _
                    & Replace(!fld6, "'", "''") & "', '" _
                    & Replace(POs(i), "'", "''") & "', '" _
                    & Replace(!fld11, "'", "''") & "', '" _
                    & Replace(!fld13, "'", "''") & "', '" _
                    & Replace(!fld14, "'", "''") & "', ')"
                ' Execute the sql
                CurrentDb.Execute strSql, dbFailOnError
            Next
            .MoveNext
        Loop
        .Close
    End With
    Set rsImport = Nothing
End Sub

Watch out for line wrap.  Hope this helps.

Rdub

---
This email has been checked for viruses by AVG.
http://www.avg.com

[toc] | [prev] | [next] | [standalone]

#13109

Just noticed a error in the last line of the Sql statement
Change
& Replace(!fld14, "'", "''") & "', ')"
To
& Replace(!fld14, "'", "''") & "')"

Might be other problems as well, but I can not test without your table 
structure.  If you can supply some DDL queries for the two tables, and 
some test data in Sql Insert statements. I'd be happy to test, and 
debug.

Rdub

---
This email has been checked for viruses by AVG.
http://www.avg.com

[toc] | [prev] | [next] | [standalone]

#13112

Hi Ron

Many thanks for your time and effort on this.

I inserted all the field names ( in their correct order ) and I noticed that there was no ( ; ) at the end of the SQL and though I should insert it. Either way, it's throwing up a Run-time Error 3134: Syntax Error in INSERT INTO statement:

            POs = Split(!txfPONo, ",")
            For i = 0 To UBound(POs)
                strSql = "Insert Into tblOrders_Inbound2 (txfLoadID,txfPONo,txfVendorID,txfVendor,txfSuburb,txfDest,txfRoute,txfDriver,txfLoadNo,dtfVenArr,dtfDCArr)" _
                    & "Values('" & Replace(!txfLoadID, "'", "''") & "', '" _
                    & Replace(POs(i), "'", "''") & "', '" _
                    & Replace(!txfVendorID, "'", "''") & "', '" _
                    & Replace(!txfVendor, "'", "''") & "', '" _
                    & Replace(!txfSuburb, "'", "''") & "', '" _
                    & Replace(!txfDest, "'", "''") & "', '" _
                    & Replace(!txfRoute, "'", "''") & "', '" _
                    & Replace(!txfDriver, "'", "''") & "', '" _
                    & Replace(!txfLoadNo, "'", "''") & "', '" _
                    & Replace(!dtfVenArr, "'", "''") & "', '" _
                    & Replace(!dtfDCArr, "'", "''") & "', " ');"

Also, not sure if this will impact it, but! both dtfVenArr & dtfDCArr are both (Short-Time) format.

I'm also curious about the "Find String As & Replace String As" as the Find is " ' " and the Replace is " ' ' " is there are Copy/Paste distinction between the two.

Many thanks again for your assistance.

Cheers
Mark.

[toc] | [prev] | [next] | [standalone]

#13113

If dtfDCArr were a string you'd want to Change
  & Replace(!dtfDCArr, "'", "''") & "', " ');"
To
  & Replace(!dtfDCArr, "'", "''") & "');"


But since it is a DateTime, you want to change
                    & Replace(!txfLoadNo, "'", "''") & "', '" _
                    & Replace(!dtfVenArr, "'", "''") & "', '" _
                    & Replace(!dtfDCArr, "'", "''") & "', " ');"
To
                    & Replace(!txfLoadNo, "'", "''") & "', #" _
                    & !dtfVenArr & "#, #" _
                    & !dtfDCArr & "#);"

The easy way to debug a dynamically created Sql statement is to put a 
breakpoint somewhere after the sql is created and before it gets 
executed.  Go to the Immediate window, and debug.print it.  Then copy 
the output to a query in Sql view, and see what Access thinks is bad.  
Fix and test again, if all OK make the change(s) in your code, and 
bada-da-bing, you are off to the races.

The replace() function doubles up any single quotes that might be in 
your data.  Imagine what the resulting string might look like if your 
data included the name O'Connor in it.  Without doubling up the single 
quote the Sql string would come out as 'O'Connor'.  Access WILL 
complain about an undelimited string.  What we want the Sql output to 
look like is 'O''Connor'.  Access will treat the double single quote as 
an Escaped single single quote, and Access will go with the flow.

I have been burned by Irish and Italian names many-many times in the 
past, so I wrap ALL string data used in my dynamicly created Sql 
statements with the Replace function to double up any embedded single 
quotes.

In this case your short DateTime fields need to surrounded by Access 
Date Delimiters (#), and there should never be any single quotes in 
your DateTime data, as that would be an invalid date which Access would 
hate.

Rdub

---
This email has been checked for viruses by AVG.
http://www.avg.com

[toc] | [prev] | [next] | [standalone]

#13114

On Tuesday, May 8, 2018 at 9:04:12 AM UTC-5, Ron Weiner wrote:
> If dtfDCArr were a string you'd want to Change
>   & Replace(!dtfDCArr, "'", "''") & "', " ');"
> To
>   & Replace(!dtfDCArr, "'", "''") & "');"
> 
> 
> But since it is a DateTime, you want to change
>                     & Replace(!txfLoadNo, "'", "''") & "', '" _
>                     & Replace(!dtfVenArr, "'", "''") & "', '" _
>                     & Replace(!dtfDCArr, "'", "''") & "', " ');"
> To
>                     & Replace(!txfLoadNo, "'", "''") & "', #" _
>                     & !dtfVenArr & "#, #" _
>                     & !dtfDCArr & "#);"
> 
> The easy way to debug a dynamically created Sql statement is to put a 
> breakpoint somewhere after the sql is created and before it gets 
> executed.  Go to the Immediate window, and debug.print it.  Then copy 
> the output to a query in Sql view, and see what Access thinks is bad.  
> Fix and test again, if all OK make the change(s) in your code, and 
> bada-da-bing, you are off to the races.
> 
> The replace() function doubles up any single quotes that might be in 
> your data.  Imagine what the resulting string might look like if your 
> data included the name O'Connor in it.  Without doubling up the single 
> quote the Sql string would come out as 'O'Connor'.  Access WILL 
> complain about an undelimited string.  What we want the Sql output to 
> look like is 'O''Connor'.  Access will treat the double single quote as 
> an Escaped single single quote, and Access will go with the flow.
> 
> I have been burned by Irish and Italian names many-many times in the 
> past, so I wrap ALL string data used in my dynamicly created Sql 
> statements with the Replace function to double up any embedded single 
> quotes.
> 
> In this case your short DateTime fields need to surrounded by Access 
> Date Delimiters (#), and there should never be any single quotes in 
> your DateTime data, as that would be an invalid date which Access would 
> hate.
> 
> Rdub
> 
> ---
> This email has been checked for viruses by AVG.
> http://www.avg.com


Get rid of all the Replace code by using parameters in the query. You are handling single and double quotes, but what about ? and *. Users have a habit of entering unexpected text. And some may try to inject SQL code. From Ron Weiner's sample code; modify the query and add the following

  
dim qryDef as DAO.QueryDef

strSql = "PARAMETERS [fld1p] Text ( 255 ), [fld3p] Text ( 255 ), [fld4p] Text ( 255 ), [fld5p] Text ( 255 ),  
[fld6p] Text ( 255 ), [POip] Text ( 255 ), [fld11p] Text ( 255 ), [fld12p] DateTime, [fld13p] IEEEDouble, [fld14p] fld14p; " & _
"Insert Into tblImport_Orders (fld1,fld3,fld4,fld5,fld6,fieldPOs,fld11,fld12,fld13,fld14 ) " & _ 
Values([fld1p], [fld2p], [fld3p], [fld4p], [fld5p], [fld6p], [POip], [fld11p], [fld13p], [fld14p]);"

set qryDef = Currentdb.CreateQueryDef("", strSql)
set qryDef.Parameters(0) = cstr(!fld1)
set qryDef.Parameters(1) = cstr(!fld1)
set qryDef.Parameters(2) = cstr(!fld3)
set qryDef.Parameters(3) = cstr(!fld4)
set qryDef.Parameters(4) = cstr(!fld5)
set qryDef.Parameters(5) = cstr(!fld6)
set qryDef.Parameters(6) = cstr(POs(i))
set qryDef.Parameters(7) = datevalue(!fld11)
set qryDef.Parameters(8) = cdbl(!fld13)
set qryDef.Parameters(9) = clng(!fld14)

qryDef.Execute dbFailOnError

set qryDef = nothing

I was unsure of the field types so I included Text, Date/Time, Double, Long. you may need to adjust for your table. The insert part of noodnutt's code did not look like field names. 

Insert Into tblOrders_Inbound2 (txfLoadID,txfPONo,txfVendorID,txfVendor,txfSuburb,txfDest,txfRoute,txfDriver,txfLoadNo,dtfVenArr,dtfDCArr)

[toc] | [prev] | [next] | [standalone]

#13120

Hi Ron W & P.

I had issues with both code updates:

Ron W.

The new Date Time change looks promising with one drawback in that it throws up an error:

Runtime Error: 3075 - Syntax in String in Query Expression " 1:00:00 PM#, #3:00:00 PM# "

This is what your code looked like when I changed to my field names.

strSql = "Insert Into tblOrders_Inbound2 (txfLoadID,txfPONo,txfVendorID,txfVendor,txfSuburb,txfDest,txfRoute,txfDriver,txfLoadNo,dtfVenArr,dtfDCArr)" _
                    & "Values('" & Replace(!txfLoadID, "'", "''") & "', '" _
                    & Replace(POs(i), "'", "''") & "', '" _
                    & Replace(!txfVendorID, "'", "''") & "', '" _
                    & Replace(!txfVendor, "'", "''") & "', '" _
                    & Replace(!txfSuburb, "'", "''") & "', '" _
                    & Replace(!txfDest, "'", "''") & "', '" _
                    & Replace(!txfRoute, "'", "''") & "', '" _
                    & Replace(!txfDriver, "'", "''") & "', '" _
                    & Replace(!txfLoadNo, "'", "''") & "', '" _
                    & !dtfVenArr & "#, #" _
                    & !dtfDCArr & "#);"
                ' Execute the sql
                CurrentDb.Execute strSql, dbFailOnError

Ron P.

When Running, it immediately throw an error @ .Parameters (0) stating:

Invalid use of Property.

Just a thought, and I should have dropped this at the beginning, I am using 2010 so not sure if that has a bearing on any code changes in later versions.

Ron P.

This is how the SQL String looked like, you may see something I have missed

strSql = "PARAMETERS [txfLoadID] Text ( 10 ),POs(i) Text ( 255 ),[txfVendorID] Text ( 35 ),[txfVendor] Text ( 150 ),[txfSuburb] Text ( 75 ),[txfDest] Text ( 25 ),[txfRoute] Text ( 7 ),[txfDriver] Text ( 35 ),[txfLoadNo] Text ( 15 ),[dtfVenArr] DateTime,[dtfDCArr] DateTime; " & _
                     "Insert Into tblOrders_Inbound2 (txfLoadID,txfPONo,txfVendorID,txfVendor,txfSuburb,txfDest,txfRoute,txfDriver,txfLoadNo,dtdVenArr,dtfDCArr & Values(txfLoadID, txfPONo, txfVendorID, txfSuburb, txfDest, txfRoute, txfDriver, txfLoadNo, dtfVenArr, dtfDCArr);"

            Set qryDef = CurrentDb.CreateQueryDef("", strSql)
            Set qryDef.Parameters(0) = CStr(!txfLoadID)
            Set qryDef.Parameters(1) = CStr(POs(i))
            Set qryDef.Parameters(2) = CStr(!txfVendorID)
            Set qryDef.Parameters(3) = CStr(!txfVendor)
            Set qryDef.Parameters(4) = CStr(!txfSuburb)
            Set qryDef.Parameters(5) = CStr(!txfDest)
            Set qryDef.Parameters(6) = CStr(txfRoute)
            Set qryDef.Parameters(7) = CStr(!txfDriver)
            Set qryDef.Parameters(8) = DateValue(!dtfVenArr)
            Set qryDef.Parameters(9) = DateValue(!dtfDCArr)

            qryDef.Execute dbFailOnError

            Set qryDef = Nothing

Once again, I am sincerely appreciative of your time and help.

Kind regards
Mark.

[toc] | [prev] | [next] | [standalone]

#13121

On Thursday, May 10, 2018 at 7:55:37 AM UTC-5, noodnutt wrote:
> Hi Ron W & P.
> 
> I had issues with both code updates:
> 
> Ron W.
> 
> The new Date Time change looks promising with one drawback in that it throws up an error:
> 
> Runtime Error: 3075 - Syntax in String in Query Expression " 1:00:00 PM#, #3:00:00 PM# "
> 
> This is what your code looked like when I changed to my field names.
> 
> strSql = "Insert Into tblOrders_Inbound2 (txfLoadID,txfPONo,txfVendorID,txfVendor,txfSuburb,txfDest,txfRoute,txfDriver,txfLoadNo,dtfVenArr,dtfDCArr)" _
>                     & "Values('" & Replace(!txfLoadID, "'", "''") & "', '" _
>                     & Replace(POs(i), "'", "''") & "', '" _
>                     & Replace(!txfVendorID, "'", "''") & "', '" _
>                     & Replace(!txfVendor, "'", "''") & "', '" _
>                     & Replace(!txfSuburb, "'", "''") & "', '" _
>                     & Replace(!txfDest, "'", "''") & "', '" _
>                     & Replace(!txfRoute, "'", "''") & "', '" _
>                     & Replace(!txfDriver, "'", "''") & "', '" _
>                     & Replace(!txfLoadNo, "'", "''") & "', '" _
>                     & !dtfVenArr & "#, #" _
>                     & !dtfDCArr & "#);"
>                 ' Execute the sql
>                 CurrentDb.Execute strSql, dbFailOnError
> 
> Ron P.
> 
> When Running, it immediately throw an error @ .Parameters (0) stating:
> 
> Invalid use of Property.
> 
> Just a thought, and I should have dropped this at the beginning, I am using 2010 so not sure if that has a bearing on any code changes in later versions.
> 
> Ron P.
> 
> This is how the SQL String looked like, you may see something I have missed
> 
> strSql = "PARAMETERS [txfLoadID] Text ( 10 ),POs(i) Text ( 255 ),[txfVendorID] Text ( 35 ),[txfVendor] Text ( 150 ),[txfSuburb] Text ( 75 ),[txfDest] Text ( 25 ),[txfRoute] Text ( 7 ),[txfDriver] Text ( 35 ),[txfLoadNo] Text ( 15 ),[dtfVenArr] DateTime,[dtfDCArr] DateTime; " & _
>                      "Insert Into tblOrders_Inbound2 (txfLoadID,txfPONo,txfVendorID,txfVendor,txfSuburb,txfDest,txfRoute,txfDriver,txfLoadNo,dtdVenArr,dtfDCArr & Values(txfLoadID, txfPONo, txfVendorID, txfSuburb, txfDest, txfRoute, txfDriver, txfLoadNo, dtfVenArr, dtfDCArr);"
> 
>             Set qryDef = CurrentDb.CreateQueryDef("", strSql)
>             Set qryDef.Parameters(0) = CStr(!txfLoadID)
>             Set qryDef.Parameters(1) = CStr(POs(i))
>             Set qryDef.Parameters(2) = CStr(!txfVendorID)
>             Set qryDef.Parameters(3) = CStr(!txfVendor)
>             Set qryDef.Parameters(4) = CStr(!txfSuburb)
>             Set qryDef.Parameters(5) = CStr(!txfDest)
>             Set qryDef.Parameters(6) = CStr(txfRoute)
>             Set qryDef.Parameters(7) = CStr(!txfDriver)
>             Set qryDef.Parameters(8) = DateValue(!dtfVenArr)
>             Set qryDef.Parameters(9) = DateValue(!dtfDCArr)
> 
>             qryDef.Execute dbFailOnError
> 
>             Set qryDef = Nothing
> 
> Once again, I am sincerely appreciative of your time and help.
> 
> Kind regards
> Mark.



1) Parameter names should not be the same as the field names. I added a "P" to the end.
2) You have 11 columns in "Insert Into" and only 10 in "Values"
3) You have 11 parameters but only supply 10 values in the qryDef.Parameters() code.

"PARAMETERS [txfLoadIDp] Text ( 10 ), [txfPONop] Text ( 255 ), 
            [txfVendorIDp] Text ( 35 ), [txfVendorp] Text ( 150 ), 
            [txfSuburbp] Text ( 75 ), [txfDestp] Text ( 25 ), 
            [txfRoutep] Text ( 7 ), [txfDriverp] Text ( 35 ),
            [txfLoadNop] Text ( 15 ), 
            [dtfVenArrp] DateTime, [dtfDCArrp] DateTime; " & _ 
"Insert Into tblOrders_Inbound2 
(txfLoadID,  txfPONo,  txfVendorID,  txfVendor,  txfSuburb, txfDest,   txfRoute,   txfDriver,  txfLoadNo,  dtdVenArr, dtfDCArr 
Values 
(txfLoadIDp, txfPONop, txfVendorIDp, txfSuburbp, txfDestp,  txfRoutep, txfDriverp, txfLoadNop, dtfVenArrp, dtfDCArrp);" 

            set qryDef = CurrentDb.CreateQueryDef("", strSql) 

4) Sorry my mistake! "SET" is not used when filling parameters

            qryDef.Parameters(0) = CStr(!txfLoadID) 
            qryDef.Parameters(1) = CStr(POs(i)) 
            qryDef.Parameters(2) = CStr(!txfVendorID) 
            qryDef.Parameters(3) = CStr(!txfVendor) 
            qryDef.Parameters(4) = CStr(!txfSuburb) 
            qryDef.Parameters(5) = CStr(!txfDest) 
            qryDef.Parameters(6) = CStr(txfRoute) 
            qryDef.Parameters(7) = CStr(!txfDriver) 
            qryDef.Parameters(8) = ?????
            qryDef.Parameters(9) = DateValue(!dtfVenArr) 
            qryDef.Parameters(10) = DateValue(!dtfDCArr)

[toc] | [prev] | [next] | [standalone]

#13124

Hi Ron P

I did the alteration as you suggested and it came up with Error 3134: Error in Insert Into highlighting the Set qryDef.

In the end I managed to get Ron W's code to work and it does what I am looking for, so for the purposes of moving things along and not waste anymore of your valuable time I will stick with it.

Thank you again for all the time and patience with this.

Very much appreciated.

Kind regards
Mark.

For your perusal, here is the whole code with both Ron W & your suggestions:

    Dim rsImport As ADODB.Recordset
    Dim i As Integer
    Dim POs() As String, strSql As String
    Dim qryDef As DAO.QueryDef

    Set rsImport = New ADODB.Recordset
    rsImport.Open "tblImport_Inbound", CurrentProject.Connection, adOpenDynamic, adLockPessimistic, adCmdTable
    With rsImport
        Do While Not .EOF
            POs = Split(!txfPONo, ",")
            For i = 0 To UBound(POs)
            strSql = "PARAMETERS [txfLoadIDp] Text ( 10 ), [txfPONop] Text ( 255 ), [txfVendorIDp] Text ( 35 ), [txfVendorp] Text ( 150 ), [txfSuburbp] Text ( 75 ), [txfDestp] Text ( 25 ), [txfRoutep] Text ( 7 ), [txfDriverp] Text ( 35 ), [txfLoadNop]  Text, [dtfVenArrp] DateTime, [dtfDCArrp] DateTime; " & _
                     "Insert Into tblOrders_Inbound2 (txfLoadID, txfPONo, txfVendorID, txfVendor, txfSuburb, txfDest, txfRoute, txfDriver, txfLoadNo, dtdVenArr, dtfDCArr & Values(txfLoadIDp, txfPONop, txfVendorIDp, txfVendorp, txfSuburbp, txfDestp, txfRoutep, txfDriverp, txfLoadNop, dtfVenArrp, dtfDCArrp);"

            Set qryDef = CurrentDb.CreateQueryDef("", strSql)
            qryDef.Parameters(0) = CStr(!txfLoadID)
            qryDef.Parameters(1) = CStr(POs(i))
            qryDef.Parameters(2) = CStr(!txfVendorID)
            qryDef.Parameters(3) = CStr(!txfVendor)
            qryDef.Parameters(4) = CStr(!txfSuburb)
            qryDef.Parameters(5) = CStr(!txfDest)
            qryDef.Parameters(6) = CStr(!txfRoute)
            qryDef.Parameters(7) = CStr(!txfDriver)
            qryDef.Parameters(8) = CStr(!txfLoadNo)
            qryDef.Parameters(9) = DateValue(!dtfVenArr)
            qryDef.Parameters(10) = DateValue(!dtfDCArr)

            qryDef.Execute dbFailOnError

            Set qryDef = Nothing
        Next
        Debug.Print
        .MoveNext
        Loop
        .Close
    End With
    Set rsImport = Nothing

[toc] | [prev] | [next] | [standalone]

#13127

On Friday, May 11, 2018 at 7:54:51 AM UTC-5, noodnutt wrote:
> Hi Ron P
> 
> I did the alteration as you suggested and it came up with Error 3134: Error in Insert Into highlighting the Set qryDef.
> 
> In the end I managed to get Ron W's code to work and it does what I am looking for, so for the purposes of moving things along and not waste anymore of your valuable time I will stick with it.
> 
> Thank you again for all the time and patience with this.
> 
> Very much appreciated.
> 
> Kind regards
> Mark.
> 
> For your perusal, here is the whole code with both Ron W & your suggestions:
> 
>     Dim rsImport As ADODB.Recordset
>     Dim i As Integer
>     Dim POs() As String, strSql As String
>     Dim qryDef As DAO.QueryDef
> 
>     Set rsImport = New ADODB.Recordset
>     rsImport.Open "tblImport_Inbound", CurrentProject.Connection, adOpenDynamic, adLockPessimistic, adCmdTable
>     With rsImport
>         Do While Not .EOF
>             POs = Split(!txfPONo, ",")
>             For i = 0 To UBound(POs)
>             strSql = "PARAMETERS [txfLoadIDp] Text ( 10 ), [txfPONop] Text ( 255 ), [txfVendorIDp] Text ( 35 ), [txfVendorp] Text ( 150 ), [txfSuburbp] Text ( 75 ), [txfDestp] Text ( 25 ), [txfRoutep] Text ( 7 ), [txfDriverp] Text ( 35 ), [txfLoadNop]  Text, [dtfVenArrp] DateTime, [dtfDCArrp] DateTime; " & _
>                      "Insert Into tblOrders_Inbound2 (txfLoadID, txfPONo, txfVendorID, txfVendor, txfSuburb, txfDest, txfRoute, txfDriver, txfLoadNo, dtdVenArr, dtfDCArr & Values(txfLoadIDp, txfPONop, txfVendorIDp, txfVendorp, txfSuburbp, txfDestp, txfRoutep, txfDriverp, txfLoadNop, dtfVenArrp, dtfDCArrp);"
> 
>             Set qryDef = CurrentDb.CreateQueryDef("", strSql)
>             qryDef.Parameters(0) = CStr(!txfLoadID)
>             qryDef.Parameters(1) = CStr(POs(i))
>             qryDef.Parameters(2) = CStr(!txfVendorID)
>             qryDef.Parameters(3) = CStr(!txfVendor)
>             qryDef.Parameters(4) = CStr(!txfSuburb)
>             qryDef.Parameters(5) = CStr(!txfDest)
>             qryDef.Parameters(6) = CStr(!txfRoute)
>             qryDef.Parameters(7) = CStr(!txfDriver)
>             qryDef.Parameters(8) = CStr(!txfLoadNo)
>             qryDef.Parameters(9) = DateValue(!dtfVenArr)
>             qryDef.Parameters(10) = DateValue(!dtfDCArr)
> 
>             qryDef.Execute dbFailOnError
> 
>             Set qryDef = Nothing
>         Next
>         Debug.Print
>         .MoveNext
>         Loop
>         .Close
>     End With
>     Set rsImport = Nothing

Well now I have to look!

You had an extra & in the strSql string in front of Values

dtfDCArr & Values( 
should have been
dtfDCArr Values(

PS: the query can be defined outside of the do loop.

    Dim rsImport As ADODB.Recordset 
    Dim i As Integer 
    Dim POs() As String, strSql As String 
    Dim qryDef As DAO.QueryDef 

    strSql = "PARAMETERS [txfLoadIDp] Text ( 10 ), [txfPONop] Text ( 255 ), 
[txfVendorIDp] Text ( 35 ), " & _
             "[txfVendorp] Text ( 150 ), [txfSuburbp] Text ( 75 ), [txfDestp] Text ( 25 ), [txfRoutep] Text ( 7 ), " & _
             "[txfDriverp] Text ( 35 ), [txfLoadNop]  Text, [dtfVenArrp] DateTime, [dtfDCArrp] DateTime; " & _ 
             "Insert Into tblOrders_Inbound2 (txfLoadID, txfPONo, txfVendorID, txfVendor, txfSuburb, txfDest, txfRoute, txfDriver, txfLoadNo, dtdVenArr, dtfDCArr " &  _
             "Values(txfLoadIDp, txfPONop, txfVendorIDp, txfVendorp, txfSuburbp, txfDestp, txfRoutep, txfDriverp, txfLoadNop, dtfVenArrp, dtfDCArrp);" 

    Set qryDef = CurrentDb.CreateQueryDef("", strSql) 

    Set rsImport = New ADODB.Recordset 
    rsImport.Open "tblImport_Inbound", CurrentProject.Connection, adOpenDynamic, adLockPessimistic, adCmdTable 
    With rsImport 
        Do While Not .EOF 
            POs = Split(!txfPONo, ",") 
            For i = 0 To UBound(POs) 

            qryDef.Parameters(0) = CStr(!txfLoadID) 
            qryDef.Parameters(1) = CStr(POs(i)) 
            qryDef.Parameters(2) = CStr(!txfVendorID) 
            qryDef.Parameters(3) = CStr(!txfVendor) 
            qryDef.Parameters(4) = CStr(!txfSuburb) 
            qryDef.Parameters(5) = CStr(!txfDest) 
            qryDef.Parameters(6) = CStr(!txfRoute) 
            qryDef.Parameters(7) = CStr(!txfDriver) 
            qryDef.Parameters(8) = CStr(!txfLoadNo) 
            qryDef.Parameters(9) = DateValue(!dtfVenArr) 
            qryDef.Parameters(10) = DateValue(!dtfDCArr) 

	    qryDef.Execute dbFailOnError
        Next          

        Set qryDef = Nothing 

[toc] | [prev] | [next] | [standalone]

#13129

Hi Ron, and thank you again..

You were correct in that I did overlook the extra ampersand, so I copy/pasted your structure, with the exception of brushing it up with my own personal layout preference, sadly though, it still halted on the same line: Set qtyDef.

    Dim rsImport As ADODB.Recordset
    Dim i As Integer
    Dim POs() As String, strSql As String
    Dim qryDef As DAO.QueryDef

        strSql = "PARAMETERS [txfLoadIDp] Text ( 10 ), [txfPONop] Text ( 255 ), [txfVendorIDp] Text ( 35 )," & _
        "[txfVendorp] Text ( 150 ), [txfSuburbp] Text ( 75 ), [txfDestp] Text ( 25 ), [txfRoutep] Text ( 7 )," & _
        "[txfDriverp] Text ( 35 ), [txfLoadNop]  Text, [dtfVenArrp] DateTime, [dtfDCArrp] DateTime;" & _
        "Insert Into tblOrders_Inbound2 (txfLoadID, txfPONo, txfVendorID, txfVendor, txfSuburb, txfDest, txfRoute, txfDriver, txfLoadNo, dtdVenArr, dtfDCArr" & _
        "Values(txfLoadIDp, txfPONop, txfVendorIDp, txfVendorp, txfSuburbp, txfDestp, txfRoutep, txfDriverp, txfLoadNop, dtfVenArrp, dtfDCArrp);"

    Set qryDef = CurrentDb.CreateQueryDef("", strSql)
        
    Set rsImport = New ADODB.Recordset
    rsImport.Open "tblImport_Inbound", CurrentProject.Connection, adOpenDynamic, adLockPessimistic, adCmdTable
    
        With rsImport
            Do While Not .EOF
                POs = Split(!txfPONo, ",")
                For i = 0 To UBound(POs)
                    qryDef.Parameters(0) = CStr(!txfLoadID)
                    qryDef.Parameters(1) = CStr(Trim(POs(i)))
                    qryDef.Parameters(2) = CStr(!txfVendorID)
                    qryDef.Parameters(3) = CStr(!txfVendor)
                    qryDef.Parameters(4) = CStr(!txfSuburb)
                    qryDef.Parameters(5) = CStr(!txfDest)
                    qryDef.Parameters(6) = CStr(!txfRoute)
                    qryDef.Parameters(7) = CStr(!txfDriver)
                    qryDef.Parameters(8) = CStr(!txfLoadNo)
                    qryDef.Parameters(9) = DateValue(!dtfVenArr)
                    qryDef.Parameters(10) = DateValue(!dtfDCArr)
                    qryDef.Execute dbFailOnError
                Next
                
    Set qryDef = Nothing
    
                .MoveNext
            
            Loop
                .Close
        End With
        
    Set rsImport = Nothing

If you wish to keep nutting it out for the sake of the challenge, please enjoy.. I am very much thankful for your assistance none-the-less.

Kind regards
Mark.

[toc] | [prev] | [next] | [standalone]

#13136

On Friday, May 11, 2018 at 9:50:03 PM UTC-5, noodnutt wrote:
> Hi Ron, and thank you again..
> 
> You were correct in that I did overlook the extra ampersand, so I copy/pasted your structure, with the exception of brushing it up with my own personal layout preference, sadly though, it still halted on the same line: Set qtyDef.
> 
>     Dim rsImport As ADODB.Recordset
>     Dim i As Integer
>     Dim POs() As String, strSql As String
>     Dim qryDef As DAO.QueryDef
> 
>         strSql = "PARAMETERS [txfLoadIDp] Text ( 10 ), [txfPONop] Text ( 255 ), [txfVendorIDp] Text ( 35 )," & _
>         "[txfVendorp] Text ( 150 ), [txfSuburbp] Text ( 75 ), [txfDestp] Text ( 25 ), [txfRoutep] Text ( 7 )," & _
>         "[txfDriverp] Text ( 35 ), [txfLoadNop]  Text, [dtfVenArrp] DateTime, [dtfDCArrp] DateTime;" & _
>         "Insert Into tblOrders_Inbound2 (txfLoadID, txfPONo, txfVendorID, txfVendor, txfSuburb, txfDest, txfRoute, txfDriver, txfLoadNo, dtdVenArr, dtfDCArr" & _
>         "Values(txfLoadIDp, txfPONop, txfVendorIDp, txfVendorp, txfSuburbp, txfDestp, txfRoutep, txfDriverp, txfLoadNop, dtfVenArrp, dtfDCArrp);"
> 
>     Set qryDef = CurrentDb.CreateQueryDef("", strSql)
>         
>     Set rsImport = New ADODB.Recordset
>     rsImport.Open "tblImport_Inbound", CurrentProject.Connection, adOpenDynamic, adLockPessimistic, adCmdTable
>     
>         With rsImport
>             Do While Not .EOF
>                 POs = Split(!txfPONo, ",")
>                 For i = 0 To UBound(POs)
>                     qryDef.Parameters(0) = CStr(!txfLoadID)
>                     qryDef.Parameters(1) = CStr(Trim(POs(i)))
>                     qryDef.Parameters(2) = CStr(!txfVendorID)
>                     qryDef.Parameters(3) = CStr(!txfVendor)
>                     qryDef.Parameters(4) = CStr(!txfSuburb)
>                     qryDef.Parameters(5) = CStr(!txfDest)
>                     qryDef.Parameters(6) = CStr(!txfRoute)
>                     qryDef.Parameters(7) = CStr(!txfDriver)
>                     qryDef.Parameters(8) = CStr(!txfLoadNo)
>                     qryDef.Parameters(9) = DateValue(!dtfVenArr)
>                     qryDef.Parameters(10) = DateValue(!dtfDCArr)
>                     qryDef.Execute dbFailOnError
>                 Next
>                 
>     Set qryDef = Nothing
>     
>                 .MoveNext
>             
>             Loop
>                 .Close
>         End With
>         
>     Set rsImport = Nothing
> 
> If you wish to keep nutting it out for the sake of the challenge, please enjoy.. I am very much thankful for your assistance none-the-less.
> 
> Kind regards
> Mark.

I took 1 more look at it. You were missing a space after the ; at the end of the Parameters clause and a closing ) and a space at the end of the Insert Into clause. 

See below.

strSql = "PARAMETERS [txfLoadIDp] Text ( 10 ), [txfPONop] Text ( 255 ), [txfVendorIDp] Text ( 35 )," & _ 
"[txfVendorp] Text ( 150 ), [txfSuburbp] Text ( 75 ), [txfDestp] Text ( 25 ), [txfRoutep] Text ( 7 )," & _ 
"[txfDriverp] Text ( 35 ), [txfLoadNop]  Text, [dtfVenArrp] DateTime, [dtfDCArrp] DateTime; " & _ 
"Insert Into tblOrders_Inbound2 (txfLoadID, txfPONo, txfVendorID, txfVendor, txfSuburb, txfDest, txfRoute, txfDriver, txfLoadNo, dtdVenArr, dtfDCArr) " & _ 
"Values(txfLoadIDp, txfPONop, txfVendorIDp, txfVendorp, txfSuburbp, txfDestp, txfRoutep, txfDriverp, txfLoadNop, dtfVenArrp, dtfDCArrp);" 

[toc] | [prev] | [next] | [standalone]

#13143

Hi Ron

thank you for nutting it out. 

everyone efforts are very much appreciated, which is more than I can say for my employer, who has decided to shelve the project and outsource it.

Meh, their money I suppose..

Thanks again.
Mark.

[toc] | [prev] | [next] | [standalone]

#13122

noodnutt

I guess you didn't take my advice on using a breakpoint, Debug.Printing 
the Sql, and pasteing it into a new query in Sql view.

I think the problem is in the Following line of code.
Try changing
       & Replace(!txfLoadNo, "'", "''") & "', '" _

To
       & Replace(!txfLoadNo, "'", "''") & "', #" _

Rdub

---
This email has been checked for viruses by AVG.
http://www.avg.com

[toc] | [prev] | [next] | [standalone]

#13125

Hi Ron

I did insert a debug.print break into the code. I am not coding via a query rather a module triggered by a command button so there was no query in which to use the SQL view.

Suffice-to-say, After changing to your final suggestion, it worked as expected, thank you so much for your efforts.

If I can bug you for one last thing in this. Is there a way to delete the leading space from all the PONo's that have been split after the comma as this will have a impact on the next process which is a Search/Match between the Orders_Inbound & Orders_Outbound.

To fill you in on why this next step is required, there are 2 types of transport, the inbound which is done by local metro trucks and the outbound which is interstate line-haul and I have to match the orders from each file so the transport hub know which metro driver is bringing the inbound orders relative to the outbound.

Many thanks
Mark.

[toc] | [prev] | [next] | [standalone]

#13126

On Friday, May 11, 2018 at 9:17:16 AM UTC-4, noodnutt wrote:
> Hi Ron
> 
> I did insert a debug.print break into the code. I am not coding via a query rather a module triggered by a command button so there was no query in which to use the SQL view.
> 
> Suffice-to-say, After changing to your final suggestion, it worked as expected, thank you so much for your efforts.
> 
> If I can bug you for one last thing in this. Is there a way to delete the leading space from all the PONo's that have been split after the comma as this will have a impact on the next process which is a Search/Match between the Orders_Inbound & Orders_Outbound.
> 
> To fill you in on why this next step is required, there are 2 types of transport, the inbound which is done by local metro trucks and the outbound which is interstate line-haul and I have to match the orders from each file so the transport hub know which metro driver is bringing the inbound orders relative to the outbound.
> 
> Many thanks
> Mark.

I'd think Trim() would work.

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | comp.databases.ms-access

csiph-web