Extract Multiple Dates from MemoField

[jmdl0518]

Hi all,
I'm brand new here and this is my first post. Hopefully I make sense and someone here can steer me in the right direction.

I was given a database that has a table with a memo field. In this memo field there are multiple dates followed by some text and then another date. I need to be able to extract those dates and add them separately to a new table so I can get a Datediff calculation. Example:

Submitted by Joe: 1/15/2015 text text text text Approved by Mike: 2/10/15.
I would need to grab 1/15/2015 and 2/10/15 and use datediff to give me the difference between dates. My problem is how to extract the two dates.

There is no standard pattern or delimiter that precedes or proceeds the dates. Is there a VBA code that could look through the memo field and grab the numerical characters and extract them to a new table? Maybe using the "/" as a delimiter? I have no idea and would appreciate any help.

 

[CJ_London]

investigate the use of regex (regular expressions) - part of your problem is that the dates are not of a consistent length - e.g. 10/12/2015, 2/12/2015

alternatively use split to split the text on / then inspect each element of the array - if it is a number then you know that the last character or two of the previous element is part of a date and the first few (always 4?) characters of the next element is the year.

Something like

 dim Sarr() as string
 dim I as integer
 dim Dateextracted as date
  
 Sarr=split(memotext,"/")
  
 for I=1 to ubound(Sarr)-1
     if isnumeric(sarr(I)) then 'it is part of a date
         dateextracted=dateserial(left(sarr(I+1),4),sarr(I),strreverse(val(strreverse(sarr(I-1)))))
         ....
         do something with dateextracted here
         ....
         ...
     end if 
 next i

 

[sneuberg]

Using regular expressions would be an interesting approach. Maybe this site would help:

http://regexlib.com/DisplayPatterns.aspx?cattabindex=4&categoryId=5&AspxAutoDetectCookieSupport=1

 

[jdraw]

It sounds like a design that is missing a table (or two) and is using a memo field in its place.
How many records with these memo fields exist?
I would look long and hard to see what effort would be needed to change the design to a proper "log" type table. It may not be practical and regex may be your best tool.
God luck with your project.

 

[Acropolis]

Just as a thought another way of doing it, but it doesn't involve VBA.


Is this just a one off thing? You want to get the data out the memo field and use it, rather than something that you will need to be able to do time and time again?


If it's just a single use, then you might be easier to export the table to Excel, use the text to columns feature to get the dates out, alter the table in Access then re-import the dates against the records in the original table (modified). But this would only be any good if it's a one off, and would only take about 10 minutes to do.


Same end result, just a different way of getting to it.

 

[jmdl0518]

CJ_London: I will look into this, Thank you for your response.

sneuberg: I will read on this and see what I can make of it. thank you.

jdraw: This table has over 35k records. I have already made the change to add the Date field in a new column, but will need to extract the existing dates from the Memo field. It is always tough when you get someone else's DB and trying to understand why they did it this way or that way.

Acropolis: I will see if I can export and do a text to column in Excel. thank you for your suggestion.

Thank you everyone who responded. I truly appreciate your thoughts and suggestions.

 

[jdraw]

For consideration.

Create a query and create a new table that has the Primary key of the record involved and the memo field with all the dates. Then use that table as a sample to develop the logic/parsing required.
You could even put that table in a database and post if for readers to suggest/try some options.

You could even do this with only 10 or 15 representative records.

Good luck.