?
Solved

Extracting Dates Stored As String From a Memo Field

Posted on 2011-03-16
10
Medium Priority
?
342 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 48

Expert Comment

by:Dale Fye
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 2000 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
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.

 
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
 
LVL 48

Expert Comment

by:Dale Fye
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 48

Expert Comment

by:Dale Fye
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

777 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