String Manipulation (1 Viewer)

[dazza61]

Hi guys,

Hope this hasn't been covered before...

We use Outlook and an application I've designed in Access at my place of work for Contact Management. We do quite a few imports and exports between both apps, some built in and some VBA coded I'm wondering how to go about the following:

In Outlook, within a Contact you could have body/notes that read:

25/10/06 Spoke to Person Y regarding Product X. Call back tomorrow

27/10/06 Person Y is interested in a meeting. Call back to arrange meeting

29/10/06 Person Y not in office today...
etc, etc....

In Outlook, the above info would be stored in the 'body' field. In my Access application, the above info would be stored in a separate 'many' table that is related to the Main Contact table. I've written a routine that grabs the date and note info from individual records in Access which then stores the concatenated result in a separate 'memo' field. I then use this 'memo' field in exports to the Outlook 'body' field...

However, I want to be able to do the whole thing in reverse. That is, grab the the first date from the above text (25/10/06), then grab the note that follows it and insert this into a record in the 'many' table in Access. Then move to the second date (27/10/06), grab this, then grab the note that follows and insert this into another record in Access, and so on...

Any ideas of the string coding I would need to achieve this?

Many thanks in advance.


dazza61

 

[Bat17]

I think that a lot will depend on the consistency of the data but for a starter I would use the strip function delimiting on the carriage return to turn the rows into an array.
test the first word of each row to see if it is a date with isdate function. then decide what to do with the row


HTH

Peter

 

[dazza61]

Thanks for the reply Peter

Your soooo right about the consistency. My example of text was an ideal example, but some users enter the date without a year (e.g. 25/10) and sometimes the date is entered in various formats (e.g. 25.10, 25-10-06). Then some may insert a new line (or more) after the date before they enter the notes for a given date......and so on.......

Any chance of some example coding. I'm not brilliant at functions, etc.

Many thanks in advance.

dazza61

 

[Bat17]

I think that you will need to teach your users how to write dates, If you stick with free-form entries then you will end up in a mess.

Peter