Create Table Using Split Command (1 Viewer)

bastanu

AWF VIP
Local time
Yesterday, 23:54
Joined
Apr 13, 2010
Messages
1,401
Try this update please:
Code:
Public Function Split()

10 On Error GoTo PROC_Error
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsOut As DAO.Recordset
Dim SplitToRows() As String
Dim i As Integer
20 Set db = CurrentDb
30 Set rsSource = db.OpenRecordset("Source")
40 Set rsOut = db.OpenRecordset("SerialSplit")
50 If (Not rsSource.BOF And Not rsSource.EOF) Then
60 rsSource.MoveFirst
70 Do Until rsSource.EOF
   If IsNull(rsSource!Serial Then GoTo Skip_To_Next
80 SplitToRows = Split(rsSource!Serial, ", ", -1)
90 For i = LBound(SplitToRows) To UBound(SplitToRows)
100 rsOut.AddNew
110 rsOut("Customer Purchase Order") = rsSource("Customer Purchase Order") ' Keep these the same
120 rsOut("Shipped to City") = rsSource("Shipped to City")
130 rsOut("Shipped to State") = rsSource("Shipped to State")
140 rsOut("Item Description") = rsSource("Item Description")
150 rsOut("Quantity") = rsSource("Quantity")
160 rsOut("Unit Price") = rsSource("Unit Price")
170 rsOut("Serial") = SplitToRows(i) ' Data was in one field - split by a comma
180 rsOut.Update
190 Next i '
Skip_To_Next:
200 rsSource.MoveNext
210 Loop
220 Else
230 MsgBox "No Records in Input"
240 End If
250 rsSource.Close
260 Set rsSource = Nothing
270 rsOut.Close
280 Set rsOut = Nothing
290 Set db = Nothing
PROC_Exit:
300 Exit Function
PROC_Error:
310 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in SplitToRows procedure"
320 On Error GoTo 0

Resume PROC_Exit:
End Function
 

DiamondGeezer

New member
Local time
Today, 06:54
Joined
Dec 23, 2021
Messages
10
I get "Compile error: Syntax error" at the new code "If IsNull(rsSource!Serial Then GoTo Skip_To_Next"
 

bastanu

AWF VIP
Local time
Yesterday, 23:54
Joined
Apr 13, 2010
Messages
1,401
Sorry, missing a bracket, should be If IsNull(rsSource!Serial) Then GoTo Skip_To_Next
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:54
Joined
May 21, 2018
Messages
8,463
30 Set rsSource = db.OpenRecordset("Select * from Source Where Serial is not null")
 

DiamondGeezer

New member
Local time
Today, 06:54
Joined
Dec 23, 2021
Messages
10
I tried the options in #23 & #24 & get a single "Error 28 (Out of Stack Space) in SplitToRows procedure" followed by many "Error 13 (Type mismatch) in SplitToRows procedure" errors using either
 

bastanu

AWF VIP
Local time
Yesterday, 23:54
Joined
Apr 13, 2010
Messages
1,401
Open the attached file and inspect the Source table (imported from your Excel file) and SerialSplit (empty). Now open the Query1 to run the code (you named the custom function Split() which conflicts with the built-in one that you use in the same function). Once the query opens look again at the SerialSplit table, should be populated with 328 records.

Cheers,
Vlad
 

Attachments

  • Database34.accdb
    480 KB · Views: 144

DiamondGeezer

New member
Local time
Today, 06:54
Joined
Dec 23, 2021
Messages
10
Thank you, that's working to create the table, and when I run it as database 34 I get 164 (Did you run it twice?) records with no issues :) However, when I copy that code to my database it runs and uncovers the next problem; unfortunately, in the full dataset, some of the first serial numbers in the Serial field have a leading space in the Source table. Is there anything I can add to selectively trim those please?
 

bastanu

AWF VIP
Local time
Yesterday, 23:54
Joined
Apr 13, 2010
Messages
1,401
Yes, wrapped them in Trim():
170 rsOut("Serial") = Trim(SplitToRows(i))
Cheers,
 

bastanu

AWF VIP
Local time
Yesterday, 23:54
Joined
Apr 13, 2010
Messages
1,401
You're very welcome, good luck with your project!
 

Users who are viewing this thread

Top Bottom