Separating fields with commas...

[dodgyone]

I have a field with an address in it... each line is separated by a comma.

The data is to be transferred into a new DB and I need to separate the data into its own fields.

So... take the first section up to the comma (into a new field), the middle section up to the next comma (into a new field) and the end section into a new field (into a new field).

I was thinking of some kind of variation of TRIM(LEFT()) etc...

Does anyone have any suggestions / code examples?

 

[MichaelRed]

Depends on your ver of Ms. Access. The older 'sisters' (ver < 2K) could use basSplit (posted in these forums several times), while the youngest 'sister' (ver 2K) can use the new intrinsic function Split. Either way, you do need some code to do the actual assignment of the set of values to the appropiate fields. Also, address information is seldom consistient in the actual implementtation, so you will need some additional parseing to decode which field a specific 'string' should be placed into.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[dodgyone]

I have Access 97... does anyone have any coding ideas?

Thanks...

 

[THoey]

As MichaelRed mentioned, if you search this forum for basSplit, you will find the code you need... Terry M. Hoey