Solved

Extracting Dates Stored As String From a Memo Field

Posted on 2011-03-16
10
335 Views
Last Modified: 2012-06-27
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
Comment
Question by:error_prone
  • 5
  • 3
  • 2
10 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 35149139
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
 
LVL 6

Accepted Solution

by:
AkAlan earned 500 total points
ID: 35149193
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
 

Author Comment

by:error_prone
ID: 35149591
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
 
LVL 6

Expert Comment

by:AkAlan
ID: 35149805
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
 

Author Comment

by:error_prone
ID: 35150335
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 35150493
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
 

Author Comment

by:error_prone
ID: 35152941
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 35153178
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
 

Author Comment

by:error_prone
ID: 35153504
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
 

Author Closing Comment

by:error_prone
ID: 35157272
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now