Create Table Using Split Command

[bastanu]

Try this update please:

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]

I get "Compile error: Syntax error" at the new code "If IsNull(rsSource!Serial Then GoTo Skip_To_Next"

 

[bastanu]

Sorry, missing a bracket, should be If IsNull(rsSource!Serial) Then GoTo Skip_To_Next

 

[MajP]

30 Set rsSource = db.OpenRecordset("Select * from Source Where Serial is not null")

 

[DiamondGeezer]

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]

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

 

[DiamondGeezer]

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]

Yes, wrapped them in Trim():
170 rsOut("Serial") = Trim(SplitToRows(i))
Cheers,

 

[DiamondGeezer]

Yes, wrapped them in Trim():
170 rsOut("Serial") = Trim(SplitToRows(i))
Cheers,

Brilliant, thank you. That's working like a charm

 

[bastanu]

You're very welcome, good luck with your project!