Splitting a text field conatining a carriage return.

[Greg Teets]

I have a field in an access 2000 database that is actually three lines
of text separated by carriage returns.

Is there a function in the Access version of SQL that will let me
locate the carriage returns and then split the field based on the
positions of the carriage returns?

Or do I need to process the field with Visual Basic?

I seached for an answer to this on the web. I found an answer that
said to use the CHARINDEX function but that is apparently not a part
of ACCESS 2000 because I get an error message saying that it is
invalid.

Thanks in advance for your help.

Greg Teets
Cincinnati Ohio USA

[fredg]

On Thu, 30 Dec 2004 20:06:19 GMT, Greg Teets wrote:

I have a field in an access 2000 database that is actually three lines
of text separated by carriage returns.

Is there a function in the Access version of SQL that will let me
locate the carriage returns and then split the field based on the
positions of the carriage returns?

Or do I need to process the field with Visual Basic?

I seached for an answer to this on the web. I found an answer that
said to use the CHARINDEX function but that is apparently not a part
of ACCESS 2000 because I get an error message saying that it is
invalid.

Thanks in advance for your help.

Greg Teets
Cincinnati Ohio USA

If your version of Access has the Split() function (look it up in VBA
Help), you can use it to parse the field into 3 different parts.
You would use Chr(13) & chr(10) as the field delimiter.

Because this is a VBA function, you'll need to create a module, then
call it from the query.

In a module:

Public Function splitthis(field in, X)
Dim var As Variant
var = Split(fieldin, Chr(13) & Chr(10), -1)
splitthis = var(X)

End Function

In a query:
New:splitthis([FieldName],0)

will get the first part. The parts are Zero based, so the third part
will be
splitthis([FieldName],2)

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.