Solved

String Manipulation

Posted on 2011-03-17
25
270 Views
Last Modified: 2012-05-11
I have a memo field in a table that doesn't belong to me that I need to query from. There are dates appended to multiple comments in one memo record like below. I need to extract the first date entered as a string, (and turn it into a date). So in the example below, I would need 03/16/11. This is just an example.

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

I was able to get some traction with this formula.

Last_Contact_Dt: Left(Replace(Replace([Last Contact],Chr(10),""),Chr(13),""),InStr(1,Replace(Replace([Last Contact],Chr(10),""),Chr(13),"")," ")-1)

It returns values from the table like this:

03/16/11;

02/10/11Closed

3-15-11-OUT-Received

How can I remove all characters to the right of the last number? So the values returned would be:

03/16/11
02/10/11
3-15-11
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
  • 12
  • 10
  • 2
  • +1
25 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 35157658
Can you establish that the date will be the left-most string, and that it will be 6-8 characters in length?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35157684
<I have a memo field in a table that doesn't belong to me that I need to query from.>
<How can I remove all characters to the right of the last number? >

The simplest solution, form where I stand, would be to update all of the data to include a space after the date.

This way instead of try to detect "all characters to the right of the last number", you simply take everything to the left of the first space:
    CDate(Left([YourField],InStr([YourField]," ")-1))

But I am sure that there may be an expert along that can help you with your specific requirement...

JeffCoachman

 
0
 

Author Comment

by:error_prone
ID: 35157695
Can you establish that the date will be the left-most string, and that it will be 6-8 characters in length?

It should be. If it's not, it's fine to return NULL.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 26

Expert Comment

by:jerryb30
ID: 35157718
I would think the isDate function would be of use.
Maybe set up a function checking 6-n leftmost charaters of the string.
function getDate(yourstring as string) as date
for i= 6 to 10
if isdate(left(yourstring, i) then
getdate = cdate(left(yourstring,i)
exit function
endif
next i

which you call with Last_contact: getDate([Contact Date
0
 

Author Comment

by:error_prone
ID: 35157720
Well, they could also choose to enter the full year like YYYY.  That's why I was wondering if there was a way to look at the last numeric character and remove everything after that.
0
 

Author Comment

by:error_prone
ID: 35157738
if isdate(left(yourstring, i) then
getdate = cdate(left(yourstring,i)

Is highlighted in red...
0
 

Author Comment

by:error_prone
ID: 35157784
CDate(Left([YourField],InStr([YourField]," ")-1))

I'm getting an #Error for every record using this.
0
 
LVL 40

Expert Comment

by:als315
ID: 35157801
You can try this function:
 
Function leftD(A As String)
Dim Sl As Integer, i As Integer, j As Integer
Sl = Len(A)
For i = Sl To 1 Step -1
        j = Asc(Mid(A, i - 1, 1))
        If j >= 48 And j <= 57 Then
            leftD = Left(A, i - 1)
            Exit For
        End If
Next i
End Function

Open in new window

I suppose there are no digits after your date.
0
 

Author Comment

by:error_prone
ID: 35157828
als315:
I'm gettng invalid procedure or call argument
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35157840
<<I have a memo field in a table that doesn't belong to me that I need to query from.>

You might also want to convey the importance of "Normalizing" the date into it's own field, to the "Owner" of this data.
Remember that another benefit of doing this is that you can "validate" the Date easily.
If you leave this as a memo field the user can enter *anything* as a date:
1/1/-2011
5/5/5
Sept-89
Sept - 89
12-Sep
Wednesday, March 29th 2000
Wed, Mar 11
Jan. sixteenth 1980
Nov 56, 2000
12/99/99
10/10
Friday
Today
Yesterday
2001
ad infinitum...
;-)

With a structure like what you have now, you will always have to resort to "Jerking around" with the data.
...Then validating it to see if it is even a valid date!...
;-)


JeffCoachman
0
 

Author Comment

by:error_prone
ID: 35157859
LOL...Owner is long gone.  We have a new app in the works but the legacy data still needs to be reported on.
0
 

Author Comment

by:error_prone
ID: 35157863
3 guesses as to why the Owner is long gone.
0
 

Author Comment

by:error_prone
ID: 35157869
Any work-arounds guys?  I desperately need a solution.
0
 
LVL 26

Accepted Solution

by:
jerryb30 earned 500 total points
ID: 35157928
Working.

Function getdate(yourstring As String) As Date
Dim i As Integer
For i = 10 To 6 Step -1
If IsDate(Left(yourstring, i)) Then
getdate = CDate(Left(yourstring, i))
Exit Function
End If
Next i


End Function

last_Contact: getdate([Last Contact])
0
 

Author Comment

by:error_prone
ID: 35157974
That works!! Any way to get rid of the #Error but still return the record when the field is NULL.  That means there has never been an attempted contact.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 35157990
Last_Contact:IIf(getdate([last contact])=#12:00:00 AM#,Null,getdate([last contact]))
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 35158011
The #error# only occurs using your string manipulation.  The function, for some reason, returns 12:00:00 AM when the leftmost characters cannot resolve to a date.

0
 

Author Comment

by:error_prone
ID: 35158018
Hmm...still returning an #Error.
0
 

Author Comment

by:error_prone
ID: 35158024
Ok...one sec.
0
 

Author Closing Comment

by:error_prone
ID: 35158043
Awesome!!
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 35158155
Thanks.  You are bound to run into some other conditions, as jeff mentioned, in which you want to preserve date info on badly formatted data. Depending on number of cases, it might be easier to change to a date by hand.  I am thinking of how the function might go through and find the first date, even if it is not leftmost.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 35161816
Air code

Function getdate(yourstring As String) As Date
Dim i As Integer
dim n as integer
for n = 1 to len(yourString)-6
if isnumeric(mid(yourstring,n)) then
For i = 10 To 6 Step -1
If IsDate(Left(yourstring, i)) Then
getdate = CDate(Left(yourstring, i))
Exit Function
End If
next i
endif
next n



End Function
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 35161923
Forget that.

This works if date begins with a number.

Working an algorithm for dates with just Month, year. I know if I did a series of if statements for the beginning of month names, it would work, but I am trying to shorten code. This is just an exercise.

Function getdate(yourstring As String) As Date
Dim i As Integer
Dim MonthStr
MonthStr = ("'Jan', 'Feb', 'mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'")
'create strings to check for month beginnings
'don't know if if statement will use IN function for instring = mid(yourstring, i,3)
Dim n As Integer
For n = 1 To Len(yourstring) - 6
If IsNumeric(Mid(yourstring, n, 1)) Then
For i = 10 To 6 Step -1
If IsDate(Mid(yourstring, n, i)) Then
getdate = CDate(Mid(yourstring, n, i))
Exit Function
End If
Next i
End If
Next n
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 35161982
This works even if the month is abbreviated, but the year has to be yyyy

Function getdate(yourstring As String) As Date
Dim i As Integer
Dim MonthStr
MonthStr = ("'Jan', 'Feb', 'mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'")
'create strings to check for month beginnings
'don't know if if statement will use IN function for instring = mid(yourstring, i,3)
Dim n As Integer
For n = 1 To Len(yourstring) - 6
'If IsNumeric(Mid(yourstring, n, 1)) Then
For i = 16 To 6 Step -1
If IsDate(Mid(yourstring, n, i)) Then
getdate = CDate(Mid(yourstring, n, i))
Exit Function
End If
Next i

Next n



End Function
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 35162117
Well, may 11 doesn't work, but may 011 does.
as does sept 11
Must be an 'undocumented feature'.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

628 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