Extracting a date value from memo field, different locations in memo

[pieler8]

Hello,

I'm trying to figure out a formula to extract the date from memo
fields that look like the following.

Racer x - Pending, PPP expires 12-13-10. www 10-13-10.
Racer Y Pending PPP expires 12/28/10
Allstar TT - in pending status. PPP expires on 11/19/10.
John Doe, Pending, PPP expires 12-31-10

In the results I would something like.

12-13-10
12/28/10
11/19/10
12-31-10

I can do the cdate and formatting stuff later on. I'm having trouble
with the first part. thanks

 

[John Spencer]

The only way I can see to do this at all is with VBA code.

I might try something like the following untested function.

Public Function fGetFirstDateString(strIn)
Dim vs as Variant
Dim vReturn as Variant
Dim iLoop as Integer

vReturn = Null 'Default

vS = Split(strIn & ""," ")

For i = LBound(vS) to UBound(vS)
If IsDate(vS(i)) Then
vReturn = vS(i)
exit For
End If
Next i

fGetFirstDateString = vReturn

End Function


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[James A. Fortune]

Hello,

I'm trying to figure out a formula to extract the date from memo
fields that look like the following.

Racer x - Pending, PPP expires 12-13-10. www 10-13-10.
Racer Y    Pending PPP expires 12/28/10
Allstar TT -  in pending status. PPP expires on 11/19/10.
John Doe, Pending,  PPP expires 12-31-10

In the results I would something like.

12-13-10
12/28/10
11/19/10
12-31-10

I can do the cdate and formatting stuff later on. I'm having trouble
with the first part. thanks

Here's an off-the-wall method using a technique demonstrated by Jamie
Collins in this NG:

tblI
ID AutoNumber
I Long
ID I
1 1
2 2
3 3
....
255 255
....
Up to the number of characters of the memo to check (don't blame me
for query errors if your table doesn't go high enough)

tblMemo
SID AutoNumber
theMemo Memo
1 a;sdl10/12/08;laksjdf11-11-09
2 er5/1/11tuk,oyio;ygo;hi;

qryDateSubstrings:
SELECT tblMemo.MemoID, CDate(Mid(tblMemo.theMemo,tblI.I,tblI_1.I-tblI.I
+1)) AS theDate
FROM tblMemo, tblI, tblI AS tblI_1
WHERE tblI.I<tblI_1.I And tblI.I<=Len(theMemo) AND tblI_1.I
<=Len(theMemo) AND IsDate(Mid(tblMemo.theMemo,tblI.I,tblI_1.I-tblI.I
+1))<>False AND Len(Mid(tblMemo.theMemo,tblI.I,tblI_1.I-tblI.I+1))=8;

!qryDateSubstrings:
MemoID theDate
1 10/12/2008
1 11/11/2009

A second query could use those results to grab the first date for each
MemoID. The query worked for a test memo longer than 255 characters
with a date at the end. The method is full of pitfalls.

If the first memo is changed to 'a;sdl10/12/08;laksjdf11-11-09-10',
then a third date (11/9/2010) gets added to the output of the query.
Note that 5/1/2011 would also be detected as a date when a string
length of 8 is chosen so the approach you use should depend on what
you know about your data. Loosening up the string length to a range
between 6 and 8 detects additional formats, such as those resulting
from a 'm/d/yy' format, but can also generate spurious dates spun from
parts of dates if four digit years are present in any of the dates.

James A. Fortune
(e-mail address removed)