Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    String format (ACCESS 2003)

    I have a table which has a single field filled with more content that should be. Having 14, 800+ entries of names needing to be split up and destined for a junction table.

    Current Table

    ID Name
    100 Lyudmila KONDRATYEVA (RUS), Marlies GOHR (GDR), Ingrid AUERSWALD (GDR)
    300 A. J. KITT (USA)
    400 SALSARO

    New Table - New field Country

    ID Name Country
    100 Lyudmila KONDRATYEVA RUS
    100 Marlies GOHR GDR
    100 Ingrid AUERSWALD GDR
    300 A. J. KITT USA
    400 SALSARO

    Any suggestions on how I can seperate the names while looping through a recordset. Is INSTR the best method route, or are there any other ways. Thanks

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

    Re: String format (ACCESS 2003)

    I have attached a sample database with a module basConvert that fills the new table. The code uses DAO, so you need to have a reference to the Microsoft DAO 3.6 Object Library in Tools | References (in the Visual Basic Editor).

    To adapt the code for your own use, replace the following items:

    tblOld: name of current table
    ID: ID field
    TheName: name field
    tblNew: name of new table (must exist already)
    Country: country field
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: String format (ACCESS 2003)

    Hans, what can I say!! Thankyou very,very much. It works like a dream. I envisaged hours of headaches trying to fathom that one out. Very best regards and thanks again.

  4. #4
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: String format (ACCESS 2003)

    Having a closer look today at the results of the data cleanup, I noticed several records slipping through the net caused by users entering the data in other ways. A real mess, as nobody was checking. I tried to do a db, but could not get it's size down, but have attached a small spreadsheet to give an example. The column NName is the original table field. The Athlete and Country fields are in the new table. The format users should have used are A name, a country in brakets - 3 digit, and a further field which is to be dropped, so data (JAP/Honda), Jap goes into the country field and anything after the slash gets dumped. The first few records of the spreadsheet need not be filtered, I am getting those inputs retyped. If you can throw any light on the last entries it would be very much appreciated. I must learn afterwards about the syntax for finding charcters. Many thanks
    Attached Files Attached Files

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

    Re: String format (ACCESS 2003)

    With situations like this, one has to find a balance - the last few problem cases would take an excessive amount of programming. It's more efficient to process these by hand.
    The code in the attached text file handles the case of an extra name between parentheses (it is simply skipped), and also drops the text after a slash /.
    It doesn't handle the list of referees correctly because that has a completely different structure - I'd remove those from the data and enter them separately.
    Attached Files Attached Files

  6. #6
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: String format (ACCESS 2003)

    Many thanks Hans, thats a huge improvement. I will get the badly entered data re-entered and I'm moving again. I am changing the database to control what characters and what's allowed in fields. Am greatly appreciative as with all my replies I have had on this site. Best regards, thanks again

Posting Permissions

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