Solved

Extracting Dates Stored As String From a Memo Field

Posted on 2011-03-16
10
336 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

932 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

10 Experts available now in Live!

Get 1:1 Help Now