extracting a date from a memo field 1

[jasperjasper]

I have a memo field that may or may not contain a date embedded in the data like so...
THIS IS A TEST TO SHOW HOW IT MIGHT 05/15/2003 MORE TEXT

I am using INSTR inside a loop which for some reason works for the first 10 or so records then hangs up....
Does anyone have a routine that can extract the date....

Thanks much

 

[Zion7]

My first reaction, is to use InStr(memo,"/"), what does hang up mean?

otherwise, not full proof. Actually returns 3 dates. But, may offer some ideas? (I hope)

Do Until rec.EOF

Dim st, d As String, i, x As Integer
st = "THIS IS JUST A TEST TO SEE 12/03/2004 Just because it mas huo ty ng derty"

For i = 1 To Len(st)
x = x + 1
d = Mid(st, x, 10)
Select Case Format(Format(d, "mm/dd/yyyy"), "dddd")
Case "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"
Debug.Print d & " " & Format(d, "dddd")

End Select
Next i

rec.MoveNext
Loop

again, not full proof, maybe I'm onto something?

 

[PHV]

You may consider the Split, UBound and IsDate functions.
But, be aware that strings manipulations on memo fields deal only with the first 255 characters.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Zion7]

Thanks PHV, I did a look up on IsDate, must've spelled it incorrectly.
jasper, this one returns one date, in shortdate format.
looking good, so far...?

your loop thru the records
Do Until rec.EOF

Dim st, d As String, i, x As Integer, b As Boolean
st = "THIS IS JUST A TEST TO SEE 12/03/2004 Just because it mas huo ty ng derty"

For i = 1 To Len(st)
x = x + 1
d = Mid(st, x, 10)
b = IsDate(d)
If b = True And Len(Trim(d)) = 10 Then Debug.Print d
Next i

rec.MoveNext
Loop

but, I didn't seem to have any issues with the memo field being over 255 characters. I put the date around 325 to test, no problem?

Good Luck!

 

[jasperjasper]

Thanks...I will try this routine, it looks to me that
it will work...great idea. Read thru 1 character at a time....

 

[Zion7]

jasper, thank you for the star.

I couldn't resist to modify the code, after rethinking PHV's suggestion.
I hesitated the first time, again through ignorance, not realising that the Split function will return all cases, if none specified(by default). I thought I would have to make an EXTRA loop, to get the UBound for the split variable.


Thus, as usual, PHV is right on the money.

Dim st As String, x As Integer, b As Boolean, r As Variant
st = "THIS IS JUST A TEST TO SEE 12/03/2004 Just because it mas huo ty ng derty"

r = Split(st, " ")

For x = 0 To UBound(r)
b = IsDate(r(x))
If b = True Then Debug.Print r(x)
Next x

A little more succinct than mine (I like it better).

Now, just for the record, this works provided there will always be the proper spacing around the date, meaning if there is a typo with spacing eg; ...since11/07/2004was on..., it won't work.
My 2nd post will work regardless (I'd like to think, that was intentional, LOL).

Either way, good luck!

 

[EBGreen]

A global regular expression would get all of the dates in a memo filed as well.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[jasperjasper]

I tested the routine (the first one) and it works.
Unfortunately I am using Access 97 in this case, I don't think there is a split function. If there is, i have never seen it...
Thanks so much for all your help...