Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Reply
 
Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Old 01-08-2013, 09:27 AM   #1
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,803
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Post One column has comma delimited data, split into a distinct row

Long ago, a free-form text field was created so users could enter some ID numbers. Over time, the ID numbers became multiple ID numbers in one column.
As part of an upgrade - the first 4 columns need to stay the same, but each ID Number needs to be in its own row.
There might be other examples, gave up searching for a solution and created tihs.

Example:
12 0 www State ID123, ID222, ID333
13 1 wFw Fed ID454, ID88

Becomes:
12 0 www State ID123
12 0 www State ID222
12 0 www State ID333
13 1 wFw Fed ID454
13 1 wFw Fed ID88
Code:
Option Compare Database
Option Explicit
Public Sub SplitToRows()
      '------------------------------------------------------------------
      ' Procedure : SplitToRows
      ' DateTime : 1/8/2013
      ' Author : Rx_
      ' Purpose : One field has comma delimited data - split them to multiple records
      ' Copy the primary table (Source) and delete all records - the copy becomes RsOut
      ' Replace the 1st.. 4th field names - data will remain the same for these fields
      ' Row 80 replace the field with the comma delimited data after the rsSource!
      ' Row 150 - on the rsout - add the name for the split
      ' in the Immediate window type then enter :   SplitToRows
      '------------------------------------------------------------------
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("QA_DirHorzA")      ' source table
40    Set rsOut = db.OpenRecordset("QA_HorzSplit")        ' copy of empty source table
50    If (Not rsSource.BOF And Not rsSource.EOF) Then
60        rsSource.MoveFirst
70        Do Until rsSource.EOF
80            SplitToRows = Split(rsSource!Dir_HzPass, ",", -1) ' comma - change to other delimiter here.
90            For i = LBound(SplitToRows()) To UBound(SplitToRows())
100           rsOut.AddNew
110           rsOut("ID_Wells") = rsSource("ID_Wells")    ' Keep these the same
120           rsOut("CA_Req") = rsSource("CA_Req")
130           rsOut("CA NO") = rsSource("CA NO")
140           rsOut("Lease_Type") = rsSource("Lease_Type")
              ' Data was in one field - split by a comma
150           rsOut("Dir_HzPass") = SplitToRows(i)        ' The field name that gets split based on a comma
160           rsOut.Update
170           Next i
180           rsSource.MoveNext
190       Loop
200   Else
210           MsgBox "No Records in Input"
220   End If
230   rsSource.Close
240   Set rsSource = Nothing
250   rsOut.Close
260   Set rsOut = Nothing
270   Set db = Nothing
PROC_Exit:
280   Exit Sub
PROC_Error:
290   'On Error GoTo 0 ' comment out 300 if no message box is needed
300   MsgBox "Error " & Err.Number & " (" & Err.Description & ") in splittoRows procedure"
310      On Error GoTo 0  ' clear error
 
Resume PROC_Exit:
End Sub
NOTICE: the Split function is reported to work in Office 2000 and later VBA
http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
The Following 2 Users Say Thank You to Rx_ For This Useful Post:
MOSmitty (01-09-2015), sphill0214 (06-15-2015)
Old 01-09-2015, 10:06 AM   #2
MOSmitty
Newly Registered User
 
Join Date: Oct 2013
Location: Missouri, USA
Posts: 2
Thanks: 2
Thanked 0 Times in 0 Posts
MOSmitty is on a distinguished road
Re: One column has comma delimited data, split into a distinct row

Rx, thanks for posting this script. I am using it to split error codes in a response file into their own records in a new table. The delimited field will have data such as:

0108~0204
0104

0104~0204~0508

up to four error values. The new table has two fields,

PG_Tracking - this is the unique ID field from the response file
Error_code - this is the unique error code after parsing. A sample of data in the source table is:

PG_Tracking error_code
S11000256 5002~5005~5004~5006
S1100028 5001~5002
S11000374
S11000376 5002~5050~5025
S11000378 5002
S11000530 5002

The script is working great until it runs across a record that does not have a value in the error_code field which triggers Error 94 (Invalid use of Null) in splittoRows procedure.

I do consider myself a novice VB developer (at best) and have attempted to look up how to handle null values in a loop but have been unsuccessful to this point. Can you suggest something I can add to the code that would handle the records with null value? Thanks!!
MOSmitty is offline   Reply With Quote
Reply

Tags
column , delimited , rows , split , table field

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Combine more records in one, using comma delimited dim General 3 07-23-2012 06:28 AM
Parsing comma delimited string stepone Modules & VBA 1 04-02-2004 09:59 AM
[SOLVED] Parsing Comma Delimited Lists PucSpifo Modules & VBA 4 02-25-2004 01:51 PM
Displaying a Query Column as a Comma-Delimited String rather than a Row Splinter Queries 3 07-05-2003 03:30 PM
Importing file comma delimited deekras Macros 4 03-19-2002 09:01 AM




All times are GMT -8. The time now is 04:30 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World