Solved

Extracting Dates Stored As String From a Memo Field

Posted on 2011-03-16
10
339 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

730 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