Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# String Manipulation

Posted on 2011-03-17
Medium Priority
271 Views
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

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
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
• 12
• 10
• 2
• +1

LVL 26

Expert Comment

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

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

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

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

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

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

Is highlighted in red...
0

Author Comment

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

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

LVL 40

Expert Comment

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
``````
I suppose there are no digits after your date.
0

Author Comment

ID: 35157828
als315:
I'm gettng invalid procedure or call argument
0

LVL 74

Expert Comment

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

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

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

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

Author Comment

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

LVL 26

Accepted Solution

jerryb30 earned 2000 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

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

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

LVL 26

Expert Comment

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

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

Author Comment

ID: 35158024
Ok...one sec.
0

Author Closing Comment

ID: 35158043
Awesome!!
0

LVL 26

Expert Comment

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

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

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

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

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

## Featured Post

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diâ€¦
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: â€¦
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a qâ€¦
###### Suggested Courses
Course of the Month7 days, 8 hours left to enroll