Extracting dates from a memo field

[klall]

Hello.

I need to extract date information from a memo field entered in the
following way: 01/01/2005 - 31/12/2005 01/01/2004 - 31/12/2004
01/01/2003 - 31/12/2003 01/01/1996 - 31/12/1996. The number of entries
varies.

I would welcome advice on a more elegant way of extracting all the date
information (first date, second date, third date, etc and appending to
another table, say tblDates) rather than the crude way I have used left
and mid.

Thanks in advance.

Kulwant Lall

[Baz]

<kl***@yahoo.com> wrote in message
news:11*********************@t31g2000cwb.googlegro ups.com...

Hello.

I need to extract date information from a memo field entered in the
following way: 01/01/2005 - 31/12/2005 01/01/2004 - 31/12/2004
01/01/2003 - 31/12/2003 01/01/1996 - 31/12/1996. The number of entries
varies.

I would welcome advice on a more elegant way of extracting all the date
information (first date, second date, third date, etc and appending to
another table, say tblDates) rather than the crude way I have used left
and mid.

Thanks in advance.

Kulwant Lall

The "Split" function may be of assistance. You can find it in "Help"

[ADezii]

Hello.

I need to extract date information from a memo field entered in the
following way: 01/01/2005 - 31/12/2005 01/01/2004 - 31/12/2004
01/01/2003 - 31/12/2003 01/01/1996 - 31/12/1996. The number of entries
varies.

I would welcome advice on a more elegant way of extracting all the date
information (first date, second date, third date, etc and appending to
another table, say tblDates) rather than the crude way I have used left
and mid.

Thanks in advance.

Kulwant Lall

'Here is a routine that I wrote for you which will extract all 10 character
'date values from a Memo field called [MyMemo] and print them to the Debug
'Window. Should you need the additional code to write them to a Table,
'please let me know. Don't forget, the code is based on the assumption that
'all Date fields are of the format MM/DD/YYYY or DD/MM/YYYY. Hope this helps:

================================================== ========

Dim MyDB As Database, MyRS As Recordset, T As Integer

Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblEmployees", dbOpenSnapshot)

Do While Not MyRS.EOF
If Not IsNull(MyRS![MyMemo]) Then
For T = 1 To Len(MyRS![MyMemo])
If Mid(MyRS![MyMemo], T, 1) = "/" Then
If IsDate(Mid(MyRS![MyMemo], T - 2, 10)) And Len(Mid(MyRS![MyMemo], T - 2, 10)) = 10 Then
Debug.Print Mid(MyRS![MyMemo], T - 2, 10)
End If
End If
Next T
End If
MyRS.MoveNext
Loop

MyRS.Close
================================================== ========

[pietlinden]

use Split to get the dates into an array, then step through the array
to write them to a table.

You failed to mention something, though. Are the date pairs the _only_
thing in the memo field? That's key, because otherwise, you may be
trying to parse text and treat it as if it were a date, which won't
work so well.

This should at least get you started...

'01/01/2005 - 12/31/2005 01/01/2004 - 12/31/2004
'01/01/2003 - 12/31/2003 01/01/1996 - 12/31/1996 4/1/2006 - 5/3/2006

Public Function SplitDateRanges(ByVal strDateList As String)
Dim strTemp As String
Dim varDateList As Variant
Dim varStartEndDates As Variant
Dim intCounter As Integer

strTemp = Replace(strDateList, " - ", "-", 1)
SplitDateRanges = strTemp
varDateList = Split(strTemp, " ")

For intCounter = 0 To UBound(varDateList)
If intCounter Mod 2 = 0 Then 'second of the pair..

'Debug.Print "varDateList(" & intCounter & "): " &
varDateList(intCounter)
varStartEndDates = Split(varDateList(intCounter), "-")
For intcounter2 = 0 To UBound(varStartEndDates)
Debug.Print varStartEndDates(intcounter2)
Next intcounter2
End If
Next intCounter
End Function

[klall]

Thank you very much for your help. The code works well. The date pairs
are the only thing in the memo field. The final step will be to write
to a table.

Best wishes

Kulwant

[klall]

Thanks for your input. It was very useful.

Best wishes

Kulwant