Splitting Data into seperate columns

[wisey01]

Hey all,

I have been given the task of organising a mail-merge with a sharepoint list, but the names and emails attached to each object are seperated by a delimiter. Furthermore each person is associated with many objects, and they want the merge programmed to only send 1 email to each person.

So what I need to do is split the data in one column into three separate columns, and then perform a concatenate. The concatenate isn't an issue, but does anyone have any idea how to split the data into 3 new columns within Access?

If worst comes to worst I'll tell them they will have to use the text-to-columns function in excel first, but would like to try and avoid that where possible.

Thanks in Advance,

Greg

 

[Mihail]

Take a look to this function.
Seems to be exactly what you are looking for.

Are you very sure that you need to put the results in "columns" ?
Why you not calculate the concatenate string after you split the initial one ? Or I don't understand the entire problem ?

 

[wisey01]

I'm kinda new to this still, so I will explain the whole issue.

So I have been given a list of information. This list outlines the people responsible for each program that we test, as well as version numbers, application numbers etc. and it contains about 1000 programs. I need to extract the contact information for these programs in a way that I can mail merge via email. Unfortunately, there are 3 different contact fields, and the information within each field is not uniform, with there being up to 3 different people listed as contacts within a single field.

How would I code in the split function? Do I need to save it as a Module? I managed a work around using the inbuilt Len, Right, Left, InStr and InStrRev functions, but it seems like using split would be a hell of a lot easier...

It turns out I don't need to do the concatenate anymore, as they want the information for each program separated in the email now. Which of course brings me to the problem of Mail Merging to E-Mail....

 

[pr2-eugin]

Using a Split function 'might' need a module.. I have seen using the Split function in Queries on its own before, but I am unable to find that thread.. Until then, I would prefer using Mid/InStr functions.. As using the Split would require you to specify which element you want..

 

[Mihail]

To use an external function (if I understand well the "in module" expression) is not so bad.
That because when you define your own function you can be as flexible as necessary (or as you can be, based on your skill).
It is also a "dark" side (but this is the life): the speed is dramatically decreased.

On my own, I have enough time to stand by few seconds. For the man in Moon this seconds can make the difference between life and dead.