By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,374 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Splitting a text field conatining a carriage return.

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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(fieldin, 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.
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.