Help with code!!!

  • Thread starter Thread starter SF
  • Start date Start date
S

SF

I just got a table from friend having a weird village column.

ProjectNum VillageID
3445 8090506,8090507,8090508,8090509,8090510,8090511

Now I want to convert/extract the VillageID field that separate by comma and
put it in another table sothat a can link thid ID to the Village Table. Is
there any code that do the trick?

SF
 
You could use Split function to get an array of separated values:

Dim values() As String
Dim i As Integer
values = Split(myLongCommaSeparatedValue, ",")

' now iterate through the values
For i = LBound(values) To UBound(values)
Debug.Print values(i)
Next

HTH
 
The following code is *UNTESTED* but it should be mainly ok.

Say your current table is called T1.

1. Create a new table called T2 with the same fields - but in T2, make
the VillageID field Numeric, not Text.

2. Create a new module and add the code that is shown below. Make sure
you copy & paste it exactly. Do not retype it manually!

3. Save the module.

4. Type Ctrl-g to go to the Debug window.

5. Type "Doit" (without the quotes) and press Return.

With luck, this will populate the new table within a few seconds.

If you run the code more than once, just make sure that you manually
delete any existing records from T2, first.


*UNTESTED* code follows:


public sub doit()
dim db as database, rsIn as recordset, rsOut as recordset
dim s as string, s2 as string, n as integer
set db = currentdb()
set rsIn = db.openrecordset("SELECT * FROM T1")
set rsOut as db.openrecordset("SELECT * FROM T2")
while not rsIn.eof
s = trim$(rsIn![VillageID]) & ", " ' comma space!
do while len(s) > 2 ' WHILE? DO WHILE? I CAN'T REMEMBER!! :-((
n = instr (s, ",") ' never 0 here.
s2 = rtrim$(left$(s, n-1))
s = ltrim$(mid$(s, n+1))
with rsOut
.addnew
![ProjectNum] = rsIn![ProjectNum]
![VillageID] = val(s2)
.update
end with
wend
rsIn.movenext
wend
set rsIn=nothing
set rsOut=nothing
set db=nothing

(END CODE)

Note: *UNTESTED* !

HTH,
TC (MVP Access)
http://tc2.atspace.com
 
Back
Top