Zack:

The simplest way is, as John describes, to format the string and then apply
the CDate function to it. You might be interested to know, however, that you
can return the month as a number from the name of the month with:

Month("1 " & strMonth)

The current year is assumed when omitted from an expression like this. Your
ToDate function would have worked if you'd done this rather than trying to
apply the CInt function to the name of the month.

Ken Sheridan
Stafford, England

"fallowfz" wrote:

Hello,

I'm trying to convert a text field to a new date field within my
database. The existing text field has the following format:

"DDMMMYYYY:HH:MM:SS" (e.g., "24APR2008:14:12:17").

In Desin View, this field is formatted as "GeneralDate" in the field
properties, but set to "Text" in the Date Type column. When I tried
to change the Date Type to "Date/Time", I get an "not enough memory"
error (my database has 830K lines in one table).

I tried to convert the field via a query using the following formula
(in query SQL view)...

SELECT CDate(Left([ExistingTexField],2) & "-" & Mid([ExistingTexField],
3,3) & "-" & Mid([ExistingTexField],6,4) & " " & Right
([ExistingTexField],9)) AS NewDateField
FROM MyTable;

When I run the query, I get an "#Error" in the NewDateField for each
row.

I also tried to create an update query using a module (trick I found
on this forum):

Module code (module name = mdl_ToDate)...

Public Function ToDate(ByVal DateString As String) As Date

Dim strYear As String
Dim strMonth As String
Dim strDay As String

strDay = Left$(DateString, 2)
strMonth = Mid$(DateString, 3, 3)
strYear = Mid$(DateString, 6, 4)

ToDate = DateSerial(CInt(strYear), CInt(strMonth), CInt(strDay))

End Function

Update query SQL...
UPDATE MyTable SET MyTable.NewDateField= ToDate([Run_DateTimeStamp]);

I added the "NewDateField" to the existing table, saved, and closed
then ran the query. The query returned the NewDateField, but each row
was empty...no errors or anything.

Any help would be greatly appreciated!

-Zack