manipulate text in text field

[Ron_dK]

I have some data in a text field like :
001000 , 002000, 003000 etc.

In stead of having this in one field/record I would like to have the data in a single field/record, like record 1 : 001000, record 2 - 002000, etc.

For reference see attached file, field 2.

Any idea how to "automate" this change.
Any suggestion is appreciated.

Thks, Ron

 

[dcx693]

The easiest way to separate out these strings is to write a VBA program to go row-by-row, search out the strings separated by commas and write the results to another table.

If your data is not large and this is a one-time thing, use Excel's Text to Columns.

 

[neileg]

I agree with dcx693 that Excel is the tool for a one off.

If you have a finite and smallish number of codes to extract, you could write a query that chops up the field into separate fields, write a series of queries to extract these into records with one code each (ditching any records with nulls in this field) and write a union query to pull this back into one dataset. Once set up, this will work everytime.

Hey, it won't be elegant, but it will work.

 

[Ron_dK]

I was out a couple of days, hence my late reply, but thanks for your suggestions.

DCX693 , I'm not a star in VBA programming, could you give me a hint how this string separated comma search thing would look like.

The data is updated monthly so I would love to program it in Access, rather than go through all of it in Excel.

Thanks in advance.

Ron

 

[niki]

hey!!!
glad to see I am not the only one to go through this problem!!! but ron you are a lucky man I just solved it!!
I am not sure whether you want to keep all your separated fields on a single entry or if you want each field associated with a single entry.
Go to this thread, it discusses and explains how to parse each value in a single field on a single entry, its explained by pooka tech:
http://www.access-programmers.co.uk/forums/showthread.php?threadid=50108&goto=newpost

On the contrary, if you only want your field to be separated in multiple fields on the same entry go there:
http://www.mvps.org/access/strings/str0003.htm

Both of these solutions use VB codes but they require minimum knowledge in Access to be used.
wish u success
nico

 

[Ron_dK]

Hi Nico,

This is good stuff.
I'll try the single field on a single entry, cause basically that's what I wanted.
Thanks for helping me out .

Ron