How to split hypen from text in Access 2010

H

HC11112

Registered User.
Local time
Today, 05:19
Joined
May 4, 2015
Messages
13
I have been doing an exercise to split the data from the list of the information into column A, B & C. Unfortunately the data value that i have consist of several set of data format therefore it create an issue for me to separate the information in Access 2010 easily. :(

eg.

Original Data Field
FLRY-B-0.75-L-GY
FLRY-4.0-V-R
VTAC-GY 19X20
COT-F2-5-DL

Actual Data To Populate Into Column A, B & C should be the following :

A
FLRY-B
FRY
VTAC-GY
COT-F2

B
0.75
4
19X20
5

C
L-GY
V-R
(No data captured for this column)
DL
 
Last edited:
Sorry - but I do not think you will be able to do it successful, because there are no clear delimiters between your data!
 
yeah it looks like you've used a hyphen as both text and a deliminater. I'm just thinking if you can seperate them based on the what is to the left and right of the number field, although one of those does contain a letter so cannot be sure how consistant your data is.
 
Yes, that is the situation I'm facing now as most of the data values doesn't have a standard way of data format. Therefore I have decided to break down and group the same data set value into different category.

eg. Group 1
FLRY-B-0.75-L-GY
FLRY-4.0-V-R
COT-F2-5-DL

Numeric no who appeared in the middle will be input into column B and any values appear before and after the numeric will be store into column A & C respectively....

Group 2
VTAC-GY 19X20

As for this, any data value exist before the space will be put into column A and 19X20 will be move to column B

Possible to do it this way? Please kindly advice.
 
I've made an example for you in the attached database, open the only form in it and click the button, the result will be stored in the TheColumnTable.
 

Attachments

Thanks for your support but too bad my pc don't support this database format...possible to save in access 2010 or show the example here.....?
 
Works for me, a nice bit of coding and ty for introducing me to the Split function.
 
HC11112 said:
Thanks for your support but too bad my pc don't support this database format...possible to save in access 2010 or show the example here.....?
Click to expand...
It is in MS-Access 2010.
Create 2 tables with the name and field name shown in the picture.
Create a form but a button on it, and paste the code in the on click event for the button.
Code: 
  Dim dbs As DAO.Database, rst As DAO.Recordset, rstInsert As DAO.Recordset, splString() As String
  Dim x As Integer, y As Integer, InsertString As String
  
  Set dbs = CurrentDb
  dbs.Execute ("Delete * from TheColumnTable")
  Set rst = dbs.OpenRecordset("TheDataTable")
  Set rstInsert = dbs.OpenRecordset("TheColumnTable")
  If Not rst.EOF Then
    Do
      If InStr(1, rst![Original_Data_Field], " ") Then
        rstInsert.AddNew
        rstInsert![Column_A] = Left(rst![Original_Data_Field], InStr(1, rst![Original_Data_Field], " ") - 1)
        rstInsert![Column_B] = Right(rst![Original_Data_Field], Len(rst![Original_Data_Field]) - InStr(1, rst![Original_Data_Field], " "))
        rstInsert.Update
      Else
        splString() = Split(rst![Original_Data_Field], "-")
        For x = 0 To UBound(splString)
          If IsNumeric(splString(x)) Then
            InsertString = ""
            For y = 0 To x - 1
              InsertString = InsertString & splString(y) & "-"
            Next y
            rstInsert.AddNew
            rstInsert![Column_A] = Left(InsertString, Len(InsertString) - 1)
            rstInsert![Column_B] = splString(x)
            InsertString = ""
            For y = x + 1 To UBound(splString)
              InsertString = InsertString & splString(y) & "-"
            Next y
            rstInsert![Column_C] = Left(InsertString, Len(InsertString) - 1)
            rstInsert.Update
          End If
        Next x
      End If
      rst.MoveNext
    Loop Until rst.EOF
  End If
attachment.php
 

Attachments

  • TableNames.jpg
    TableNames.jpg
    32.4 KB · Views: 384
TBH maybe the problem lays deeper. Where did you get your Original_Data_field data? Did it come like that from another table or did you get it by I/O parsing a file?
 
JHB said:
It is in MS-Access 2010.
Create 2 tables with the name and field name shown in the picture.
Create a form but a button on it, and paste the code in the on click event for the button.
Code: 
  Dim dbs As DAO.Database, rst As DAO.Recordset, rstInsert As DAO.Recordset, splString() As String
  Dim x As Integer, y As Integer, InsertString As String
  
  Set dbs = CurrentDb
  dbs.Execute ("Delete * from TheColumnTable")
  Set rst = dbs.OpenRecordset("TheDataTable")
  Set rstInsert = dbs.OpenRecordset("TheColumnTable")
  If Not rst.EOF Then
    Do
      If InStr(1, rst![Original_Data_Field], " ") Then
        rstInsert.AddNew
        rstInsert![Column_A] = Left(rst![Original_Data_Field], InStr(1, rst![Original_Data_Field], " ") - 1)
        rstInsert![Column_B] = Right(rst![Original_Data_Field], Len(rst![Original_Data_Field]) - InStr(1, rst![Original_Data_Field], " "))
        rstInsert.Update
      Else
        splString() = Split(rst![Original_Data_Field], "-")
        For x = 0 To UBound(splString)
          If IsNumeric(splString(x)) Then
            InsertString = ""
            For y = 0 To x - 1
              InsertString = InsertString & splString(y) & "-"
            Next y
            rstInsert.AddNew
            rstInsert![Column_A] = Left(InsertString, Len(InsertString) - 1)
            rstInsert![Column_B] = splString(x)
            InsertString = ""
            For y = x + 1 To UBound(splString)
              InsertString = InsertString & splString(y) & "-"
            Next y
            rstInsert![Column_C] = Left(InsertString, Len(InsertString) - 1)
            rstInsert.Update
          End If
        Next x
      End If
      rst.MoveNext
    Loop Until rst.EOF
  End If
attachment.php
Click to expand...

Awesome job!!!
I'm still afraid of more inconsistencies in the data to become apparent down the road. D:
 
:eek::cool:

brilliant, i have tried and it works very well for me. Thanks so much to JHB, this is really a great one.

Btw, something to add on, for this split job, would like to find out whether is there anyway i can still keep and store the original field into column D which work as a linking key for some other queries?

Example:
Group 1
FLRY-B-0.75-L-GY
FLRY-4.0-V-R
COT-F2-5-DL

Result
Column A
FLRY-B
FLRY
COT-F2

Column B
0.75
4.0
5

Column C
L-GY
V-R
DL

Column D
FLRY-B-0.75-L-GY
FLRY-4.0-V-R
COT-F2-5-DL
 
Last edited:
To JHB, awesome job....thanks for showing and giving me the chance to learn this splitting function...:D:p
 
Last edited:
HC11112 said:
..
Btw, something to add on, for this split job, would like to find out whether is there anyway i can still keep and store the original field into column D which work as a linking key for some other queries?
Click to expand...
Create a Column_D field in the table.
Code: 
  Dim dbs As DAO.Database, rst As DAO.Recordset, rstInsert As DAO.Recordset, splString() As String
  Dim x As Integer, y As Integer, InsertString As String
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("TheDataTable")
  Set rstInsert = dbs.OpenRecordset("TheColumnTable")
  If Not rst.EOF Then
    Do
      If InStr(1, rst![Original_Data_Field], " ") Then
        rstInsert.AddNew
        rstInsert![Column_A] = Left(rst![Original_Data_Field], InStr(1, rst![Original_Data_Field], " ") - 1)
        rstInsert![Column_B] = Right(rst![Original_Data_Field], Len(rst![Original_Data_Field]) - InStr(1, rst![Original_Data_Field], " "))
        'rstInsert.Update
      Else
        splString() = Split(rst![Original_Data_Field], "-")
        For x = 0 To UBound(splString)
          If IsNumeric(splString(x)) Then
            InsertString = ""
            For y = 0 To x - 1
              InsertString = InsertString & splString(y) & "-"
            Next y
            rstInsert.AddNew
            rstInsert![Column_A] = Left(InsertString, Len(InsertString) - 1)
            rstInsert![Column_B] = splString(x)
            InsertString = ""
            For y = x + 1 To UBound(splString)
              InsertString = InsertString & splString(y) & "-"
            Next y
            rstInsert![Column_C] = Left(InsertString, Len(InsertString) - 1)
            'rstInsert.Update
          End If
        Next x
      End If
      rstInsert![Column_D] = rst![Original_Data_Field]
      rstInsert.Update
      rst.MoveNext
    Loop Until rst.EOF
  End If
Read my signature! :) :)
 
Hi JHB,

Thanks for your prompt advice again. I have tried however it keep hitting run time errors.
 

Attachments

  • Run Time Error.PNG
    Run Time Error.PNG
    4.5 KB · Views: 139
  • Error 2.PNG
    Error 2.PNG
    19 KB · Views: 157
HC11112 said:
Hi JHB,

Thanks for your prompt advice again. I have tried however it keep hitting run time errors.
Click to expand...
You haven't past in the code I gave you, therefore you're getting the errors. Be aware of the 2 lines comment out " 'rstInsert.Update"
 
Possible to upload the database here as i still hitting issue =p
 
Last edited:
From what I can see, the rstUpdate!AddNew is within a deeper scope than your rstUpdate![Column ID] =

This is what is probably creating the problems.
 
Thanks so much... the coding really cool n powerful =)
 
You're welcome, good luck.
 
You must log in or register to reply here.

Similar threads

N
Solved How to encode some field from Json string into the related tables in MS Access
Replies
17
Views
2,010
nector
N
N
How to connect users to from Ms Access to SQL Server Express
Replies
7
Views
2,626
CJ_London
C
S
How to get data in Form from Table
Replies
4
Views
559
SachAccess
S
J
Link Multiple Access Table Fields in to a Single Excel File
Replies
4
Views
894
JithuAccess
J
I
How to calculate total in access query (Specific question)
Replies
8
Views
1,424
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom