Solved

String Manipulation

Posted on 2011-03-17
25
265 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
  • 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
 
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 39

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

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
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…

863 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

24 Experts available now in Live!

Get 1:1 Help Now