How to convert this text...Thanks

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I have a field in a table which keeps the sumary of the history of events on
a product. The field is designed as memo datatype and it adds the new event
information to the existing one. For each event the details is stored in the
record not more than 4 to 5 seperate lines of information which I code the
text strings with "hard return" to seperate it out. Over the time the amount
of information is become very large and as a result it is no longer accept
further information to add on it. I wonder if I eliminate all the "hard
returns" on that record and replace it with a space or ";" symbol and
hopfully it will increase the storing capacity on that record. My question
is how to do mass replace of the "hard return" using an Access query. There
are over 10000 records in the table. The following is an example of it

Event: Merge, Date :21-Dec-04
Merge at the Location: MAY 01
Quantity: 8

Initially Created By Yearly New Physical Count 2004, Date: 9-Oct-2004

Event: Merge, Date :21-Dec-04
Merge at the Location: MAY 01
Quantity: 3

Initially Created By Yearly New Physical Count 2004, Date: 9-Oct-2004

Event: Merge, Date :21-Dec-04
Merge at the Location: MAY 01
Quantity: 2

Initially Created By Yearly New Physical Count 2004, Date: 9-Oct-2004

Initially Created By Yearly New Physical Count 2004, Date: 9-Oct-2004


I want to make it to look like this

Event: Merge, Date :21-Dec-04 Merge at the Location: MAY 01 Quantity: 8;
Initially Created By Yearly New Physical Count 2004, Date: 9-Oct-2004;
Event: Merge, Date :21-Dec-04 Merge at the Location: MAY 01 Quantity: 3;
Initially Created By Yearly New Physical Count 2004, Date: 9-Oct-2004;
Event: Merge, Date :21-Dec-04 Merge at the Location: MAY 01 Quantity: 2;
Initially Created By Yearly New Physical Count 2004, Date: 9-Oct-2004;
Initially Created By Yearly New Physical Count 2004, Date: 9-Oct-2004

There will be no "hard return" to seperate the lines or events and each
event will be seperated out with a ";" symbol. Thanks.
 
I have a field in a table which keeps the sumary of the history of events on
a product. The field is designed as memo datatype and it adds the new event
information to the existing one.
Click to expand...

Then your table is misdesigned. Storing a one to many relationship in
a single field IS BAD DESIGN.

If you're storing history, you'll do much MUCH better to have a second
History table in a one to many relationship to your current table.
This second table would have fields for the Event, the EventDate, the
"Merge at the location" whatever that is, and the quantity; you'ld add
one record for each event.
For each event the details is stored in the
record not more than 4 to 5 seperate lines of information which I code the
text strings with "hard return" to seperate it out. Over the time the amount
of information is become very large and as a result it is no longer accept
further information to add on it.
Click to expand...

Add to that the fact that it is *impossible* to reliably search this
mishmosh for events as of a certain date, or a date range, and you
have more and more good reasons to normalize!
I wonder if I eliminate all the "hard
returns" on that record and replace it with a space or ";" symbol and
hopfully it will increase the storing capacity on that record. My question
is how to do mass replace of the "hard return" using an Access query. There
are over 10000 records in the table. The following is an example of it

Event: Merge, Date :21-Dec-04
Merge at the Location: MAY 01
Quantity: 8

Initially Created By Yearly New Physical Count 2004, Date: 9-Oct-2004

Event: Merge, Date :21-Dec-04
Merge at the Location: MAY 01
Quantity: 3

Initially Created By Yearly New Physical Count 2004, Date: 9-Oct-2004

Event: Merge, Date :21-Dec-04
Merge at the Location: MAY 01
Quantity: 2

Initially Created By Yearly New Physical Count 2004, Date: 9-Oct-2004

Initially Created By Yearly New Physical Count 2004, Date: 9-Oct-2004


I want to make it to look like this

Event: Merge, Date :21-Dec-04 Merge at the Location: MAY 01 Quantity: 8;
Initially Created By Yearly New Physical Count 2004, Date: 9-Oct-2004;
Event: Merge, Date :21-Dec-04 Merge at the Location: MAY 01 Quantity: 3;
Initially Created By Yearly New Physical Count 2004, Date: 9-Oct-2004;
Event: Merge, Date :21-Dec-04 Merge at the Location: MAY 01 Quantity: 2;
Initially Created By Yearly New Physical Count 2004, Date: 9-Oct-2004;
Initially Created By Yearly New Physical Count 2004, Date: 9-Oct-2004

There will be no "hard return" to seperate the lines or events and each
event will be seperated out with a ";" symbol. Thanks.
Click to expand...

You could use the Replace() function to update this... but seriously,
this design IS SIMPLY WRONG and patching it up will make it both wrong
and unreadable.

Invest the effort to parse this memo field out into a table instead.
YOu'll be glad you did.

John W. Vinson[MVP]
 
Back
Top