[Webinar] Streamline your web hosting managementRegister Today

x
Solved

# String Manipulation

Posted on 2011-03-17
Medium Priority
276 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
• 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 41

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Implementing simple internal controls in the Microsoft Access application.
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: …
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
###### Suggested Courses
Course of the Month7 days, 21 hours left to enroll