Parse a Multi-Line Memo Field into Multiple Fields

[mab9]

I'm trying to work with output from one of our systems and it exports it's workflow history in a single text string with each of it's steps split by a line break, ie:

"02/11/10 09:38:03;Index;Enter
02/11/10 09:38:03;Transport;Enter
02/18/10 10:12:01;Index;Exit;USERID;10113"

Since it's all stored in a single field with the line breaks, it's pretty much impossible to do anything with in it's current state. Is there anything that could transform this data some? Ideally would want to put it into something like..

Index Enter; Index Exit; Transport Enter; Transport Exit
2/11/09; ; 2/11/09; 2/18/09

 

[pbaldy]

From the look of the data (inconsistent), it will be tricky, but one tool you'll likely need is the Split() function. You can apply the split function first on the overall field using line break as the delimiter (vbCrLf), then loop through that array and apply the Split() function again, using the semicolon as the delimiter. You can then loop through that array and take the appropriate actions on each value.

 

[mab9]

Thanks Paul, I've give it a shot. I was initially trying out using replace() in a query based on chr(10) and/or chr(13) but was running into where it was only altering the first line of the string. Will see how this goes.