String format (ACCESS 2003)

[ZOR]

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

[HansV]

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

[ZOR]

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.

[ZOR]

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

[HansV]

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.

[ZOR]

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