Concatenation Dilemma

[Don Hicks]

Hello,

I'm using Access '97 to process some data that has been imported from
another database.

The information, as it came from the original database, contains text fields
that have been divided over multiple records.

This is what it once looked like:

Record Text
0001 This is the text in the first field.
0002 This is the text in the second
0003 and third fields.
0004 This is the text in the fourth &
0005 fifth fields as well as a sixth
0006 field.

About a year ago, several people in this newsgroup helped me to solve a
similar problem using a Concatenate function in VB, so now each record has
the entire text field rather than a part of it.

My new dilemma is that the text field has somehow picked up or added
carriage returns at the ends of each original line in the text fields, such
that the entire text field, even though it contains all the data, is
forcibly wrapped within the field.

This is what the new information looks like:

Record Text
0001 This is the text in the first field.
0002 This is the text in the second
and third fields.
0003 This is the text in the fourth &
fifth fields as well as a sixth
field.


And, here is what I want it to look like:

Record Text
0001 This is the text in the first field.
0002 This is the text in the second and third fields.
0003 This is the text in the fourth & fifth fields as well as a
sixth field.

Is there a way to query a field, stripping carriage returns, or just
replacing all carriage returns within the field with spaces?

Sincerely,
Don Hicks
Portland, OR

 

[Todd Shillam]

Just an idea here...but have you thought about exporting to Excel, then
import into an Access table? This 'might' do the trick. Just thought I'd
throw out a suggestion.

Good luck,

Todd

Hello,

I'm using Access '97 to process some data that has been imported from
another database.

The information, as it came from the original database, contains text fields
that have been divided over multiple records.

This is what it once looked like:

Record Text
0001 This is the text in the first field.
0002 This is the text in the second
0003 and third fields.
0004 This is the text in the fourth &
0005 fifth fields as well as a sixth
0006 field.

About a year ago, several people in this newsgroup helped me to solve a
similar problem using a Concatenate function in VB, so now each record has
the entire text field rather than a part of it.

My new dilemma is that the text field has somehow picked up or added
carriage returns at the ends of each original line in the text fields, such
that the entire text field, even though it contains all the data, is
forcibly wrapped within the field.

This is what the new information looks like:

Record Text
0001 This is the text in the first field.
0002 This is the text in the second
and third fields.
0003 This is the text in the fourth &
fifth fields as well as a sixth
field.


And, here is what I want it to look like:

Record Text
0001 This is the text in the first field.
0002 This is the text in the second and third fields.
0003 This is the text in the fourth & fifth fields as well as a
sixth field.

Is there a way to query a field, stripping carriage returns, or just
replacing all carriage returns within the field with spaces?

Sincerely,
Don Hicks
Portland, OR

 

[DBarker]

Amazing your situation sounds similar to mine, but I am
trying to get the answer to the beginning part that you
have resolved. I have CaseID that are the same the
difference are the Notes field and a date field and I am
trying to concatenate so that there is one case id that
includes all th notes on that case. Can you enlighten me?

Debbie

 

[Duane Hookom]

There is a generic function and demo in a sample database at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Duane Hookom]

You can use the Replace() function to replace Chr(13) & Chr(10) with a
space. I don't recall if Replace() is supported in 97...

 

[DBarker]

Duane,
I got that file that was converted to Access 97 but it
gets error messages and the concatenate does not work/.

 

[Duane Hookom]

And the error messages were...?
And "does not work" means...?
Is there an offending line of code? Is the returned value wrong? Does the
code even return a value?

 

[Don Hicks]

Debbie,

Actually it was Duane Hookom, who has sent several replies to this inquiry,
who provided me with the answers I needed that first time, a year ago.

(Thanks, Duane!)

Don Hicks
Portland, Oregon
=====================

 

[Don Hicks]

Todd,

That was a great idea! Since this is a one-time-only report I'm creating,
there's no need at the moment to find a way to remove the carriage returns
every time I run a query.

I like Duane's idea of using the Replace() function, but it looks like his
suspicion is right--it's not supported in Access '97.

Fortunately, there's an upgrade on my horizon, and after next month, I'll be
able to use Replace() in whatever newer version of Access I'm given!

Thanks,
Don Hicks
Portland, OR

=============