Solved Help to Split field to new column and multiple rows (1 Viewer)

[lookforsmt]

HI! All, Hope everyone over here are safe.

I have a table with 4fields: MachineId; AccountNos; TransactionRef; and MICR_Line

i want to split the MICR_Line field into multiple rows keeping the MachineId; AccountNos & TransactionRef field constant.

MICR_Line text contains below details

<MICRCodeLine>c004208d103320182dc0496726257c</MICRCodeLine><MICRCodeLine>c004208d103320182dc0496726257c</MICRCodeLine><MICRCodeLine>c004208d103320182dc0496726257c</MICRCodeLine>

i want to split the data as below

0042081033201820496726257
0042081033201820496726257
0042081033201820496726257

 

[lookforsmt]

HI! i have attached db just for better clarity. Can anyone here help me pls.

 

[lookforsmt]

i have found a function given my MarkK which i have placed in the module. I am able to remove the unwanted characters from the string. Now i want to move each split character to next row.
i have placed updated db v1
Any suggestions pls

 

[strive4peace]

hi lookforsmt,

I added this field to write the result into, since you didn't say what it is called and I didn't want to overwrite the source string:

• MICRcode, short text

here is some code you can use:

Sub SplitMakeRecords()
'200410 strive4peace
   'CALLS
   '   GetAllTheDigits

   Dim sLine As String _
      , sAccountNos As String _
      , sMachineID As String _
      , sTransactionRef As String _
      , sPart As String _
      , sMICRcode As String _
      , nCountRecords As Long _
      , i As Integer _
      , j As Integer

   Dim aPart() As String

   Dim db As DAO.Database _
      , rs As DAO.Recordset

   nCountRecords = 0

   Set db = CurrentDb
   Set rs = CurrentDb.OpenRecordset("tbl_Transactions")

   With rs
      Do While Not .EOF
         j = 0
         sLine = Nz(!MICR_Line, "")
         sMachineID = nz(!MachineId,"")
         sAccountNos = Nz(!AccountNos, "")
         sTransactionRef = Nz(!TransactionRef, "")
         If sLine <> "" Then
            aPart = Split(sLine, "<MICRCodeLine>")
     
            For i = LBound(aPart) To UBound(aPart)
               sMICRcode = ""
               sPart = Trim(Replace(aPart(i), "</MICRCodeLine>", ""))
               If Len(sPart) <> 0 Then
                  sMICRcode = GetAllTheDigits(sPart)
               End If
               If Len(sMICRcode) > 0 Then
                  j = j + 1
                  If j > 1 Then
                     .AddNew
                     if sMachineID <>"" then !MachineId = sMachineID
                     If sAccountNos <> "" Then !AccountNos = sAccountNos
                     If sTransactionRef <> "" Then !TransactionRef = sTransactionRef
                  Else
                     .Edit
                  End If
                  !MICRcode = sMICRcode
                  .Update
                  nCountRecords = nCountRecords + 1
               End If
            Next i
         End If
         .MoveNext
      Loop
   End With

   MsgBox "Added or Edited " & nCountRecords & " records", , "Done"

proc_exit:
   On Error Resume Next
   If Not rs Is Nothing Then
      rs.Close
      Set rs = Nothing
   End If
   Set db = Nothing
   Exit Sub

proc_err:
   MsgBox Err.Description, , "ERROR " & Err.Number
   Resume proc_exit
   Resume
 
End Sub

Function GetAllTheDigits(psStringWithNumbers As String) As String
'080806, 171012, 200410 strive4peace
   'PARAMETERS
   '  psStringWithNumbers is the string with digits and characters
   Dim sNumber As String _
      , i As Integer _
      , sChar As String * 1

   sNumber = ""
   'loop through and only keep numeric characters
   For i = 1 To Len(psStringWithNumbers)
      sChar = Mid(psStringWithNumbers, i, 1)
      If IsNumeric(sChar) Then
         sNumber = sNumber & sChar
      End If
   Next i
   GetAllTheDigits = sNumber
End Function

 

[strive4peace]

I didn't put many comments in, lookforsmt, so if you need anything explained, just ask!

When I started writing the code, you hadn't posted a database so I created a little table to use ...

 

[strive4peace]

ps, lookforsmt

I also didn't know what data type to use for Machineid -- I assumed it is a long integer. Now that I downloaded your database, I see that it is text. Hang on, I'll edit the code!

 

[strive4peace]

hi lookforsmt (what is your name?)

I edited the code for your names, and tested!

add this field to your table:

• MICRcode, short text

 

[lookforsmt]

Thanks crystal, for the code. it removes the unwanted characters from MICR_Line. Sorry not mentioned the end goal.
i wanted to further split the MICR_Line and limit it to 25 characters anything above 25 moves to next row. Is this possible
thanks again

 

[lookforsmt]

My Name is Mahesh and i am from India

 

[lookforsmt]

HI! crystal.
below is my end goal. If you note, MachineId; AccountNos & TransactionRef is constant for the first records as there were 3 splits in MICR_Line and 2 in the next one.

MachineId    AccountNos    TransactionRef        New_MICR_Line
E4003302    1012345678901    E4003302200884777    0042081033201820496726257
E4003302    1012345678901    E4003302200884777    0042091033201820496726257
E4003302    1012345678901    E4003302200884777    1420831033201821496726245
E4005501    1015123456701    E4005501200901085    4022052011320583912871095
E4005501    1015123456701    E4005501200901085    4022062011320583912871095

 

[strive4peace]

hi Mahesh,

you're welcome

I don't believe that MCR_Line should be changed -- you need to be able to see what the data used to be. What you are calling New_MICR_Line, I called MICRcode. I edited the code I posted before to make MachineID a string, and I added your tablename too.

Press F5 to refresh this page and get the changed code .

> further split the MICR_Line and limit it to 25 characters anything above 25 moves to next row. Is this possible

yes. I modified part of the code -- but first go get the code I posted before since I changed it!

            For i = LBound(aPart) To UBound(aPart)
               sMICRcode = ""
               sPart = Trim(Replace(aPart(i), "</MICRCodeLine>", ""))
               If Len(sPart) <> 0 Then
                  sMICRcode = GetAllTheDigits(sPart)
               End If
               Do While Len(sMICRcode) > 0
                  j = j + 1
                  If j > 1 Then
                     .AddNew
                     If sMachineID <> "" Then !MachineId = sMachineID
                     If sAccountNos <> "" Then !AccountNos = sAccountNos
                     If sTransactionRef <> "" Then !TransactionRef = sTransactionRef
                  Else
                     .Edit
                  End If
                  'just get 1st 25 digits
                  !MICRcode = Left(sMICRcode, 25)
                  .Update
                  nCountRecords = nCountRecords + 1
                  If Len(sMICRcode) > 25 Then
                     'digits after 25th one
                     sMICRcode = Mid(sMICRcode, 26)
                  Else
                     sMICRcode = ""
                  End If
               Loop
            Next i

I hope this is what you need. I must run! I'll check back later to see if you have any questions.

 

[lookforsmt]

Thanks, crystal. I have refreshed your code and replaced with the earlier code.
My only question is, do i need to put the code behind a form OnClick event and run it.
if you can post the updated db with the change will really help.
Thanks

 

[strive4peace]

hi Mahesh,

you're welcome

> "do i need to put the code behind a form OnClick event and run it. "

no, it is even easier than that! Click anywhere in the SplitMakeRecords procedure and press the F5 key to run it! Alternately, you can choose "Run Sub/Userform" from the Run menu

> if you can post the updated db with the change will really help

no, it would not help you for me to do this!

you have everything you need. It's easy once you get the hang of it

1. go to the Visual Basic Editor -- press Alt-F11
2. From the menu, choose Insert, Module
3. Paste the first code I gave you (that I changed)
4. from the menu, choose: Debug, Compile
the code I gave you shouldn't have any problems
5. Save -- click the Save icon, or choose File, Save from the menu
6. Name this module: mod_SplitMakeRecords

====================
Now you're ready to make the changes!

find the line in the module that says:
For i = LBound(aPart) To UBound(aPart)

and the line that says:
Next i

replace those 2 lines and everything between with the modification you wanted to stop lines at 25 characters.

then Debug, Compile again
then Save again

====================
The only thing you might have to change is the name of the field to write the result into. I called this
MICRcode
but you call it
New_MICR_Line

so change !MICRcode to !New_MICR_Line

then Debug, Compile again
then Save again

====================
Now you're ready to run!
click in the code and press the F5 key

if your table is open when you do this, refresh the records after the MsgBox to tell you it is done.

===================

VBA is new to you, I can tell -- but don't let it scare you off. It is logic. I spent an hour to write this for you to begin with, and then more time to answer more questions. You should spent at least an hour looking at the code to try to understand what is happening. That is only fair

You can press the F1 key when your cursor is on a keyword to go to Help for that word. You can learn this!

Good luck!!! Now I really have to go ...

 

[lookforsmt]

Thank you crystal for the detailed step by step procedure. I will try this and let you know the result.

 

[lookforsmt]

Thank you very much crystal.
i blindly followed your instructions without any challenge and was able to get it working. I will try to understand your code but honestly it is difficult for me. But will make sure i use this learning in my next project.

VBA is new to you, I can tell -- but don't let it scare you off. It is logic. I spent an hour to write this for you to begin with, and then more time to answer more questions. You should spent at least an hour looking at the code to try to understand what is happening. That is only fair

Yes you are right, i have been here for so long and still my vba is very poor. i do try to understand, but has never been able to understand this unless i take on formal classes.

I want to thank you for the time you spent from your busy time. Twice you mentioned you had to leave but you stayed till you gave me the solution. I want to thank you for that.
God Bless and stay safe from all the things that is happening around.

 

[lookforsmt]

Thank you crystal. I will close this thread as solved.

 

[strive4peace]

Namaste Mahesh,

Great, thank you. Wonderful you got it to work from the steps given! Congratulations, badhaee ho! It was my pleasure, aapaka svaagat hai.

Sorry I was short on time before and didn't put more comments in the code. I have done so now, and hope you'll take the time to understand -- not sometime in the future (too easy to put off), but now.

This is a long message, and I have to split it to post. I spent lots of time putting comments in the code for you, and after doing that, felt complelled to give you more information to increase your understanding.

So, to balance time for time, you owe this learning lesson several more hours!~ they'll be good hours though, and greatly beneficial to you now and to your your future endeavors.

===============================
notes about the code

I changed the name of aPart() to be asPart()
Now that I've given it more thought, asPart is a better name than simply aPart. Names are important -- mostly to have some kind of convention so when you go back later (maybe years later!), you can know/remember more.
"a" means the variable contains an array. "s" means that the values are strings

I rearranged the order of variables in the Dim statement.

I also changed the logic to only assign sMachineID, sAccountNos, and sTransactionRef IF the MICR_Line field is filled out (no reason to do that it if it isn't-- and that may or may not happen, but, ideally, code should allow for all possibilities).

I put an error handler in before, but forgot to set it up! So having it (at the bottom) did no good! Well now I set it up too (On Error Goto proc_err)

Additional explanation:

Array
An array is a set of indexed values.
I start array variables with "a". This is a personal preference so that when I see the variable name, I know it is an array.

Dim asPart() as string
means to allocate space for an array of multiple string values, and the number of them is not known.

asPart(0) is the first value since indexes start with 0, not 1 (unless you change the default behavior)
asPart(1) is the second value
and so on

An Excel spreadsheet is a 2-dimensional array where the first element starts with 1 in each dimension.
Cells(1,2) means the value that is in row 1, column 2. The cell address is B2, which flips from the standard array definition to put column first and then row.

Error handler
To learn more about error handling, read this:
http://www.accessmvp.com/strive4peace/Code.htm

With rs
'statements
End With

Any references between WITH and END WITH that begin with ! or . refer to the rs object
Fields in the rs object are prefaced with ! (exclamation point, also called bang)
Properties and methods in the rs object are prefaced with . (period or dot)

 

[strive4peace]

Mahesh, continuing...

on learning more about VBA: I started writing a book, but only got 3 chapters done. They'd be helpful for you to read.

Learn VBA
http://www.accessmvp.com/strive4peace/VBA.htm

VBA, though, isn't that hard! It is understanding all the different kinds of object and the properties they have, and the methods which are things they can do.

For instance, a human is an object. All have propeties to describe them like height, width, weight, eye color -- but eyes are also an object with another set of properties like big, small, squinty, slanty, near-sighted, far-sighted, and more.

Methods are actions a human can do like walk, eat, and sleep. Eyes have methods like blink.

So objects have properties and methods, and they can be composed of other objects with properties and methods, and so on.

Learning how to program with Access is more about learning about the objects that Access can read and change than it is learning the basic syntax of VBA.

Reference Links:

IsNumeric function
https://docs.microsoft.com/en-us/of...erence/user-interface-help/isnumeric-function

Len function
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/len-function

With statement
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/with-statement

Recordset.EOF property
https://docs.microsoft.com/en-us/of...database-reference/recordset-eof-property-dao

Split function
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/split-function

LBound function
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/lbound-function

UBound function
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ubound-function

Replace function
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/replace-function

LTrim, RTrim, and Trim functions
https://docs.microsoft.com/en-us/of...Interface-Help/ltrim-rtrim-and-trim-functions

 

[strive4peace]

Mahesh, here is the code with comments:

Sub SplitMakeRecords()
'200410 strive4peace
'add or edit records in the tbl_Transactions table

   'CALLS
   '   GetAllTheDigits

   'set up error handler
   On Error GoTo proc_err
   
   'dimension variables
   '  sLine = Line of data that needs to be separated
   '  sPart = part of sLine that still has extra characters
   '  sNew_MICR_Line = part of sLine with only digits
   '  sMachineID = value for MachineID field
   '  sAccountNos = value for AccountNos field
   '  sTransactionRef = value for TransactionRef field
   '  nCountRecords = variable to count the number of records changed
   '  i represents a part of the string
   '  j is to break down each part since the part might be > 25 characters
   Dim sLine As String _
      , sPart As String _
      , sNew_MICR_Line As String _
      , sMachineID As String _
      , sAccountNos As String _
      , sTransactionRef As String _
      , nCountRecords As Long _
      , i As Integer _
      , j As Integer

   'dimension array
   Dim asPart() As String

   'dimension object variables
   Dim db As DAO.Database _
      , rs As DAO.Recordset

   'initialize the number of records added or edited to be zero
   nCountRecords = 0

   'set the db variable to the current database
   Set db = CurrentDb
   'set the rs variable to be
   'records in the tbl_Transactions table
   Set rs = CurrentDb.OpenRecordset("tbl_Transactions")

   'use the rs object
   With rs
      'loop through all the records
      'EOF means End of File
      Do While Not .EOF
         'we're on a record that needs to be separated
         
         'initialize j to be 0 (this is for further splitting lines
         'that are >25 characters)
         j = 0
         'read the value of the MICR_Line field
         'and assign it to the sLine variable
         'If there is no value, use a ZLS (zero-length string)
         '  (since you can't assign NOTHING -- that will get an error)
         sLine = Nz(!MICR_Line, "")
         
         'if the line had information, then do things
         If sLine <> "" Then
            'read the value of the MachineId field
            'and assign it to the sMachineID variable
            'use ZLS if there isn't a value
            sMachineID = Nz(!MachineId, "")
            'read the value of the AccountNos field
            'and assign it to the sAccountNos variable
            'use ZLS if there isn't a value
            sAccountNos = Nz(!AccountNos, "")
            'read the value of the TransactionRef field
            'and assign it to the sTransactionRef variable
            'use ZLS if there isn't a value
            sTransactionRef = Nz(!TransactionRef, "")
         
            'split the line (sLine) into parts
            'separated at <MICRCodeLine>
            'asPart is the array of all the parts
            asPart = Split(sLine, "<MICRCodeLine>")
     
            'loop through each part of the line,
            'which is now in the asPart array
            'LBound is the Lower (minimum) index for the array
            'UBound is the Upper (maximum) index for the array
            For i = LBound(asPart) To UBound(asPart)
               'initialize the value for sNew_MICR_Line to be nothing,
               'denoted by a ZLS
               sNew_MICR_Line = ""
               'strip the close tag for MICRCodeLine at the end of the part
               'Replace the close tag with ZLS
               'Trim removes leading and trailing spaces,
               'if there are any
               sPart = Trim(Replace(asPart(i), "</MICRCodeLine>", ""))
               'if there is something in this part, then call GetAllTheDigits
               If Len(sPart) <> 0 Then
                  'get just the digits from the string
                  'and assign that to the sNew_MICR_Line variable
                  sNew_MICR_Line = GetAllTheDigits(sPart)
               End If
               'because you only want lines to be 25 characters,
               'we make another loop,
               'so parts can be further separated
               'loop while the length of the part left to process is >0
               Do While Len(sNew_MICR_Line) > 0
                  'add 1 to the part of the part
                  j = j + 1
                  'if we're not on the first part, add another record
                  If j > 1 Then
                     .AddNew
                     'assign the field you want to keep the same
                     If sMachineID <> "" Then !MachineId = sMachineID
                     If sAccountNos <> "" Then !AccountNos = sAccountNos
                     If sTransactionRef <> "" Then !TransactionRef = sTransactionRef
                  Else
                     'if we're on the first part, edit the current record
                     .Edit
                  End If
                  'just get (up to the) 1st 25 digits
                  'assign this to the New_MICR_Line field
                  !New_MICR_Line = Left(sNew_MICR_Line, 25)
                  'write changes to the recordset (save)
                  .Update
                  'increment the counter for the number of records added/changed
                  nCountRecords = nCountRecords + 1
                  'if the part is >25 then
                  'assign the characters after that to sNew_MICR_Line
                  'otherwise, assign sNew_MICR_Line to be a ZLS
                  'so the loop will stop
                  '  --> Len(sNew_MICR_Line) > 0 won't be true
                  If Len(sNew_MICR_Line) > 25 Then
                     'digits after 25th one
                     sNew_MICR_Line = Mid(sNew_MICR_Line, 26)
                  Else
                     sNew_MICR_Line = ""
                  End If
               Loop 'end loop to process 25 characters at a time
            Next i 'end loop to process each part of a line
         End If 'end test to see if line had any information
         .MoveNext 'move to next record
      Loop 'end loop to go through all records in the table
   End With 'emd using the rs object

   'give user a message that it is done!
   'tell user how many records were added or edited
   MsgBox "Added or Edited " & nCountRecords & " records", , "Done"

'cleanup object variables
proc_exit:
   On Error Resume Next
   'release object variables from memory
   If Not rs Is Nothing Then
      'close the recordset
      rs.Close
      'release the recordset object variable
      Set rs = Nothing
   End If
   'release the database object variable
   Set db = Nothing
   'exit the procedure before it accidentally slips into the error handler
   Exit Sub

'error handler
proc_err:
   'give user a message with the error number and description
   MsgBox Err.Description, , "ERROR " & Err.Number
   'go to the cleanup code and then exit
   Resume proc_exit
   'if ctrl-break was pressed at message box,
   'you can set this to be the next statement
   'so you can see where the problem is
   Resume
 
End Sub

Function GetAllTheDigits(psStringWithNumbers As String) As String
'080806, 171012, 200410 strive4peace
   'PARAMETERS
   '  psStringWithNumbers is the string with digits and characters
   'dimension variables
   '  sNumber = string of numbers
   '  i = counter to loop through each character
   '  sChar = character
   Dim sNumber As String _
      , i As Integer _
      , sChar As String * 1

   'initalize string of numbers to be a zero-length string (ZLS)
   sNumber = ""
   
   'loop through each character of the string that was passed
   'i starts with 1
   'each time through the (For) loop, i is incremented by 1
   'end at the last character
   'Len = function to get the length of what is in parentheses
   'psStringWithNumbers is the string that was passed
   For i = 1 To Len(psStringWithNumbers)
      'assign the middle of the string,
      'starting at position i, with a length of 1 character,
      'to the sChar variable
      sChar = Mid(psStringWithNumbers, i, 1)
      'the IsNumeric function will return TRUE if character
      'is a digit OR the decimal separator (.)
      '  -- but there aren't any periods (are there?), so we'll ignore that
      If IsNumeric(sChar) Then
         'if the character is a number, append it
         'to the end of the sNumber variable
         sNumber = sNumber & sChar
      End If
   Next i
   'set the return value of the function to be whatever sNumber is
   GetAllTheDigits = sNumber
End Function

 

[strive4peace]

Mahesh, and finally ... not related to the code, but an observation I made

Captions mask the real fieldnames, making it harder for the developer, not easier.

I want to mention something you're doing in your tables that isn't a good idea. You're making a Caption for fields. While this is handy when designing forms to make the label friendly, and in queries to assign an alias, it has the undesired effect of not showing the developer the real field names when opening the table. This can lead to mistakes!

This screen shot shows the Caption property in the table design for the Machineid field:

And here is what you see in Datasheet view:

Users should use forms to enter and edit data. Only the developer should open tables directly.

In queries for users, you can either set the Caption property for a column or do this in the Field cell:

Machine id: MachineID

Anything before the colon will be what shows at the top of the column.

Therefore, it would be a good idea to DELETE the Caption property values in the table designs for each field

============================

well, I could go on ... but sure this has already filled up your day! Make it a great one