Avatar of LBOYLE4
LBOYLE4
 asked on

Access Code to parse a delimited string in a field in a table

I need help with code to parse a string into a column of values.  For example the string looks like this 1234_4536_4526_7896.  I need it in a table with c olumn
1234
4536
4526
7896

I need to be able to put run the code without specifying how many values are in the delimited field.  The values can vary from one to 20 or more.
Microsoft Access

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
theruck

get the values to any text editor and replace_with newline. then copy the data wherever you want. a good texteditor is textpad you would do it there in 10 seconds
LBOYLE4

ASKER
I will need to map them to a parent record.  So I need to parse them so I can write a query for a second piece of this project.  So my ultimate goal would to be able to parse the records into a table like this

Column 1  Parse row
Parent1     1234
Parent1      2345
Parent1     4563
Parent2      1123
Parent2       44563
als315

You can do it in VBA. Use split function to separate records, open your table with openrecordset, add records. If you upload sample DB with this table and resulting table (empty), you can get exact code
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
als315

Look at sample
DBparse.accdb
ASKER CERTIFIED SOLUTION
Dale Fye

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
LBOYLE4

ASKER
FYED,
What if i wanted to put the results in a static table.  Would I just replace strSQL with the table name: db.OpenRecordset(tablename)?
Dale Fye

That is what this code would do.  You would simply change the SQL used to define rsDest to refer to your table, and use the appropriate field names from that table for the original record # and for the parsed string values.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.