Solved

vba routine function needed

Posted on 2014-01-29
10
445 Views
Last Modified: 2014-01-29
hi, i am looking for a function that should do the following:

table1 has a column note
table2 has a column note

data in table1 is saved in one column separated by commas ("a,b,c,d")

function should store the above info in table2 in 4 records.
0
Comment
Question by:bfuchs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 4

Expert Comment

by:Jack Leach
ID: 39819152
Hi,

I can only hope that this is being done in an effort to properly normalize...

Try something like this (untested, add error handling, adjust types and names etc):

Sub ParseData()

  Dim rs As DAO.Recordset
  Dim v As Variant

  Set rs = CurrentDb.OpenRecordset("Table1")

  While Not rs.EOF

    'split the values into an array
    v = Split(rs("YourField"), ",")
    'insert the values into the new table
    CurrentDb.Execute "INSERT INTO Table2 (FieldName) VALUES (""" & CStr(v(0)) & """);"
    CurrentDb.Execute "INSERT INTO Table2 (FieldName) VALUES (""" & CStr(v(1)) & """);"
    CurrentDb.Execute "INSERT INTO Table2 (FieldName) VALUES (""" & CStr(v(2)) & """);"
    CurrentDb.Execute "INSERT INTO Table2 (FieldName) VALUES (""" & CStr(v(3)) & """);"
    DoEvents

    rs.MoveNext
  Wend

  rs.Close
  Set rs = Nothing
End Sub

Open in new window


You can probably manage the same thing through pure SQL as well, though this might give you a better "visual" on what's actually going on.

Note that this code makes some assumptions, particularly a) every row in Table1 has values in that field, and b) there's always exactly 4 values which are comma delimited.  Variations on this will require more logic.

hth
-jack
0
 
LVL 4

Accepted Solution

by:
Jack Leach earned 500 total points
ID: 39819177
Here's one that will handle varying numbers of data, though still reliant on a comma delimiter:

Sub ParseData()

  Dim rs As DAO.Recordset
  Dim v As Variant
  Dim i As Integer

  Set rs = CurrentDb.OpenRecordset("Table1")

  While Not rs.EOF

    If Not IsNull(rs("YourField") Then

      'split the values into an array
      v = Split(rs("YourField"), ",")

      'insert the values into the new table
      For i = 0 To Ubound(v)
        CurrentDb.Execute "INSERT INTO Table2 (FieldName) VALUES (""" & CStr(v(i)) & """);"
      Next i

      DoEvents

    End If

    rs.MoveNext
  Wend

  rs.Close
  Set rs = Nothing
End Sub

Open in new window

0
 
LVL 4

Author Comment

by:bfuchs
ID: 39819243
Hi Jack,

thanks for replying, I will test that and let you know.

btw,
You can probably manage the same thing through pure SQL as well

just wonder how is that possible, i think would go for that..
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:hnasr
ID: 39819435
Field has 3 commas. Is this a fixed format?
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39819498
hi,
no, it can have 0 to 8 commas.

e.g
a
a,b
a,b,c
a,b,c,x,y,z
0
 
LVL 4

Expert Comment

by:Jack Leach
ID: 39819507
the second function I posted will handle any amount of comments... from 0 to 32,000+ (or change i to a Long instead of Integer to get many more)
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39819525
hi Jack,
It seems that your suggestion would do the job, just waiting to hear if there is a sql solution for that, as i assume that will work much faster...

However if no one comes up with something i will definitely accept that,

Thanks very much for your quick response
Ben
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39819540
If format is not fixed, then a sub as Jack Leach suggested is fine.
0
 
LVL 4

Expert Comment

by:Jack Leach
ID: 39819578
I'm not sure of a SQL solution offhand. While I think it could be done, it would be a bit on the advanced side and I'm not as good with SQL to whip a solution like that off the top of my head the way I can with VBA.
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 39819873
Thanks again Jack,
Please if you do find an answer on the sql path, share it with us..
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question