• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

Extracting Dates Stored As String From a Memo Field

I have a memo field that stores a comments. Users ususally enter data like this.  This is all in one memo field:

03/16/11; Meeting held bla bla bla
02-22-11 Additional informatin obtained bla bla bla; 12-14 10 Obtained necessary documents bla bla bla;
11/11/2010

This memo field contains a variety of formatting. For example, a text "date" can be entered like "03/16/11" or like "02-22-11" or like "2-22-2011".  There can even be a space between the "date" like this "12-14 10".  How can I extract the top left most "date" entered as a date? So in the example above, how would I get it to return the value 03/16/11? Now if the left most value was "12-14 10" which is basically an un-translateable date, ignore it and move on to the next most recognizable date in the memo field. Thanks!  
0
error_prone
Asked:
error_prone
  • 5
  • 3
  • 2
1 Solution
 
Dale FyeCommented:
I generally don't use a single memo field for this type of stuff.  I'll have a Date/Time field with a default value of Now(), and then the memo field.  This prevents the user from having to enter the DTG and keeps each note (or memo) separate from the next.
0
 
AkAlanCommented:
Wow what a mess you must have. Just throwing out some thoughts. Assuming you have a lot more records than you want to eyball and fix I would at least break down the taks.
Not knowing exactly how many charachters are used for the date I would first create a temp table and insert the first eight charachters and the records primary key into it. I would then do a replace and replace all the dashes and spaces with a foward slash. Then sort and eyeball what you have and see if you can clean up what is left manually. once they all resemble a date you should be able to convert the field to a date field and then create a datefield in the original table and insert them all back using the primary key. If you want help with the code for this let me know. Good luck.
0
 
error_proneAuthor Commented:
This isn't my database - I just need to query from it.

AkAlan - Let's say I take the left most characters, 8 or so, and replace the dashes with a forward slash.  Wouldn't I also need to delete all alpha characters as well? How would I do that?  Also, what's the formula for replace?  Thanks!
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
AkAlanCommented:
Here is a good atricle that explains the replace function. Not sure what you mean by alpha characters

http://msdn.microsoft.com/en-us/library/ms186862.aspx
0
 
error_proneAuthor Commented:
So let's say I use this formula on the entire field, LEFT([MyMemoField],8).  Since I'm using a general number like 8, I'll most likely also retrieve the first few letters of the comment if the date entered is shorter than 8 characters.  Like this:
1/1/11 F  
How would I get rid of the letter F or any letters and symbols for that matter?  All I want are the numbers so I can convert it into a date.
0
 
Dale FyeCommented:
So, what is your purpose?  When you say you need to query from it, what are you querying on (dates)?
Do you want to find all of the potential dates and convert them into a proper date format?  Do you want to do this for hundreds of records at a time, or only the current one?

If one record at a time, you could call a subroutine or function from a shortcut menu, or a button on the form, which would review the text in the current records memo field.  I would start by creating a loop to examine each character, one at a time.  It would test to determine whether the character is numeric or not, and if not, it would continue processing.  If the character is numeric, I'd start another loop to check successive characters searching for the first alphanumeric (not a space, slash, dot, or hyphen) character.  Once I found that character I would capture all of the characters between the number and the alpha character and would replace all of the non-numeric characters (space, dot, slash, hyphen) with slashes "/".  And then would replace instances of "//" with "/" until there were none.  If this is obviously a date, I would make the change in my memo field.  If not, I would highlight the text in the textbox and popup a custom input box which recommends changing the selected text to this new value, and let the user determine whether to make the substitution.
0
 
error_proneAuthor Commented:
This isn't a form.  I need to query a table which is part of a database that I do not own.  All I need is  a query.  Basically I need the query to return all records from this table which has a memo field formatted in the fashion I have described in the original post where the date the comment was entered is in a certain time frame.  There is no separate date/time field which stores when the comment was entered.  Users are just typing in the date preceding their comments.  Does this make sense?
0
 
Dale FyeCommented:
It makes lots of sense, but with the free form text entry, you have a serious challenge

You could do something like:

SELECT [memoField] FROM [yourTable] WHERE Replace([memoField], "  ", " ") LIKE "*3?16?10*"

I use the replace function to replace instances of two consecutive spaces with a single space, then use wildcards in a Like clause to test for a string that has a particular format.  This would account for users entering / or - or . as delimeters in the date.
0
 
error_proneAuthor Commented:
Actually, I think this is closer to what I'm looking for.  I will test this solution tomorrow.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21624884.html?sfQueryTermInfo=1+10+30+date+extract+from+string
0
 
error_proneAuthor Commented:
I was able to extract the beginnng of what could be usable data but need more help.  I think I am going to break this out into seperate questions.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now