how to extract data from a memo field and put into separate fields of new table (1 Viewer)

jom1918

Registered User.
Local time
Tomorrow, 06:01
Joined
Apr 26, 2011
Messages
30
I have a notes field in the customer table that is a memo field. An example of one customer's notes field data :

<div>20.3.14 Ordered 2 cartons</div>
<div>4.3.14 Ordered 2 cartons</div>
<div>18.2.14 ordered 1 carton</div>
<div>30.1.14 ordered 3 cartons SCENTED wipes</div>
<div>14.1.14 ordered 1 carton</div>
<div>12/12/13 - Ordered 3 cartons to get through Jan 13.</div>
<div>26/11 - Ordered 2 cartons</div>
<div>12/11/13 - one carton</div>
<div>28/10/13 - Mandy still has two cartons.</div>
<div>15/8/13 – Natalie ordered 1 ctn Uns.</div>

I want to extract the date to append to a date field in a "Calls" table and the comment into a text field in the "Calls" table. Is there a way I can do this via query or code?
 

plog

Banishment Pending
Local time
Today, 14:01
Joined
May 11, 2011
Messages
11,611
Yes, but....

I think the best metaphor for this is its like taking out someone's appendix. Even bad surgeons could do that surgery in their sleep. It's a simple surgical procedure--if you are a surgeon. But if you have to you ask the question "How do I take out someone's appendix" the task is probably beyond you without a lot of reading and trial and error.

What you are asking to do is simple parsing (http://en.wikipedia.org/wiki/Parsing), looking at an amount of information and breaking it down and apart into its fundamental units and values. The example data you provided seems pretty simple to parse for someone who knows how to do it--it would probably take 30 minutes or so for that person to write something to do it.

In general, to accomplish this, I recommend you google 'data parsing' try to find some examples and read up on it. Then, since you will be using VBA, read up on that--work through some tutorials. Specifically, once you've done those 2 things, to parse your data you will need to know about RecordSets, Loops, String manipulation functions and executing SQL commands.

Give it a shot, then post any specific questions in the VBA section of this forum.
 

jom1918

Registered User.
Local time
Tomorrow, 06:01
Joined
Apr 26, 2011
Messages
30
Thanks plog. I am reading up now and I will give it a go once I have finished.
 

Users who are viewing this thread

Top Bottom