Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parsing memo field into separat table (2003/SP1)

    MyTable1 has a RefID field (number from 1-955), and a memo fldAuthors - two or more authors are separated by a semicolon ([img]/forums/images/smilies/wink.gif[/img] with no spaces. I want myTable2 to contain RefID and author name for each of the authors in fldAuthors. Some of the records have no authors, many have more than one.
    I have tried the following code, but it doesn't work - how do I modify it to do so?
    Sub myParse()
    Dim db As Database
    Dim rst1 As Recordset
    Dim rst2 As Recordset
    Dim txtField1 As String
    Dim txtField2 As String

    Set db = CurrentDb
    Set rst1 = db.OpenRecordset("Mytable1")
    Set rst2 = db.OpenRecordset("Mytable2")
    rst1.MoveFirst
    Do While Not rst1.EOF
    txtField1 = Left(rst1!Field1, InStr(rst1!Field1, " ") - 1)
    txtField2 = Mid(rst1!Field1, InStr(rst1!Field1, " ") + 1)
    Do Until InStr(txtField2, ",") < 1
    rst2.AddNew
    rst2!Field1 = txtField1
    rst2!Field2 = Left(txtField2, InStr(txtField2, ",") - 1)
    rst2.Update
    txtField2 = Mid(txtField2, InStr(txtField2, ",") + 1)
    Loop
    rst2.AddNew
    rst2!Field1 = txtField1
    rst2!Field2 = txtField2
    rst2.Update
    rst1.MoveNext
    Loop

    End Sub

    Many thanks!

    kiwi44

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parsing memo field into separat table (2003/SP1)

    The attached code assumes that MyTable1 contains fields RefID and fldAuthors, and that MyTable2 contains fields RefID and fldAuthor. RefID should NOT be the primary key in MyTable2, since multiple records with the same RefID may be added to this table.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing memo field into separat table (2003/SP1)

    Thanks, Hans - really helped me out today! However, myTable2 ended up with a lot of records with apparently blank fldAuthor entries.

    I will have a little more time over the next few days to try and sort out what is really happening and if I need to clean up the data first, but thanks for the great start.

    kiwi44

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •