Data entry problem! Need quick fix!

[mattpearcey]

I have a very stupid problem that i need some help with. Basically, i have decided to change my address field from a single memo field to seperate fields AddressLine1, AddressLine2, AddressLine3, and Town. Now, instead of changing the orginal field Address to AddressLine1, i have added the field to run along side the original so that i can throw the data already captured into the new fields. However, due to my time constraints, i have had to add data as i have built the DB, so i have 595 record on the DB that i need to change the address for.

So..does anyone have any ideas about how i can dump the original address into the newly created fields? The original address is seperated by commars. So maybe there is a way to runa macro to dump the data across? Im not sure - its just i dont want to be sat here for the next month copying and pasting the address over to the new fields!

Anyone ? PLease save me from this torture!! Thank you for your help already.

Matt Pearcey

 

[CPUburn]

hi,

you could open a recordset over the table, and looping trough it, let's call it rcs_a:


Dim i%, y%, start%
Dim str_adr$
Dim str_1$, str_2$, str_3$, str_4$

do until rcs_a.eof

y = 1
start = 0
next_str:
For i = start To Len(str_adr)
If Mid(str_adr, i, 1) <> &quot;,&quot; Then
Select Case y
Case 1
str_1 = str_1 & Mid(str_adr, i, 1)
Case 2
str_2 = str_2 & Mid(str_adr, i, 1)
Case 3
str_2 = str_2 & Mid(str_adr, i, 1)
Case 4
str_2 = str_2 & Mid(str_adr, i, 1)
End Select
Else
start = Mid(str_adr, i + 1, 1)
y = y + 1
GoTo next_str:
End If
Loop


rcs_a.edit
rcs_a!adr1 = str_1
rcs_a!adr2 = str_2
...
rcs_a.update
rcs_a.movenext

loop


i haven't tested it, but i guess it should do the trick

 

[CPUburn]

forgot s.t.

before the loop: initiate

str_adr = rcs_a!adr !


the kid.