Solved

String Manipulation

Posted on 2011-03-17
25
264 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
Comment Utility
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
Comment Utility
<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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
if isdate(left(yourstring, i) then
getdate = cdate(left(yourstring,i)

Is highlighted in red...
0
 

Author Comment

by:error_prone
Comment Utility
CDate(Left([YourField],InStr([YourField]," ")-1))

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

Expert Comment

by:als315
Comment Utility
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
Comment Utility
als315:
I'm gettng invalid procedure or call argument
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<<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
Comment Utility
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
Comment Utility
3 guesses as to why the Owner is long gone.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

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

Accepted Solution

by:
jerryb30 earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Last_Contact:IIf(getdate([last contact])=#12:00:00 AM#,Null,getdate([last contact]))
0
 
LVL 26

Expert Comment

by:jerryb30
Comment Utility
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
Comment Utility
Hmm...still returning an #Error.
0
 

Author Comment

by:error_prone
Comment Utility
Ok...one sec.
0
 

Author Closing Comment

by:error_prone
Comment Utility
Awesome!!
0
 
LVL 26

Expert Comment

by:jerryb30
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Well, may 11 doesn't work, but may 011 does.
as does sept 11
Must be an 'undocumented feature'.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
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…

772 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

11 Experts available now in Live!

Get 1:1 Help Now