Sorting misc text into new fields if data exists in record update

[fishy]

I have inherited a database where someone in their infinite wisdom asked
staff to put four key fields into a memo field and separate them with a line
break.

I am trying to tidy up the field so that I can select the data (if it
exists) into four fields:

Date of payment.
Net amount.
Tax amount.
Interest added.

As a second complexity the data doesnt exist in all of the records i.e. the
member of staff may not have required to have input interest, have put the
heading in different orders or no descriptive of the amounts. I have tried to
input wildcards but dont know where to start in terms of finding the monetary
amount that follows this descriptive and assume that I will probably have to
run several versions of the query until I get all of the variants out.

The database is large so am trying to find a way of running an update to
field and delete from the memo field rather than have to wade in the old
fashioned way. Even if I could get a large chunk of the data into the new
fields and deleted from the existing field then that would be a great help.

All I can say is thank heavens for backups as I have tried several times
with poor results. The only guarantee is locating the date but how do I get
the query to take out the descriptve AND the date and only append the date to
the date field?

 

[Jeff Boyce]

This approach may not be pretty (i.e., "elegant"), but it might work
(untested!)...

Export the memo fields and their respective rowIDs to Excel.

Try using Excel's parsing function(s) to split the memo field into (as many)
fields.

Import the parsed fields (and rowIDs) into Access.

Use queries to "distribute" the imported data as appropriate.

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[fishy]

got there eventually with about 75% automated then a lot of manual
shovelling...