Parsing a field

[Matt Hohmeister]

I have an interesting parsing problem. I have a table formatted like this:

"1","Newark;Trenton;Camden"

....and, using SQL, I'd like to convert it to this:

"1","Newark"
"1","Trenton"
"1","Camden"

Basically, I'd like to parse a theoretically unlimited number of
semicolon-separated locations such that I wind up with a table linking each
number to one location.

 

[Tom van Stiphout]

On Fri, 17 Jul 2009 17:00:03 -0700, Matt Hohmeister

Probably much easier to do in VBA than with SQL.
In pseudo-code:
Open dao recordset on the source table
Open dao recordset on the destination table
loop over the records
for each record
call the Split function to split the locations on the semicolon.
for each element of the split array
add a record to the destination table
next
next
close all objects

-Tom.
Microsoft Access MVP

 

[Matt Hohmeister]

I'm using Access 2003 and VBA 6.5. I've done plenty of SQL, but here's the
kicker: I've never even touched Visual Basic.

Can you direct me to a Web resource to basically get me started in the right
direction here? I've done C/C++ before, so I understand programming; just not
VB.

Thanks!