Convert Text field to Date field within existing database

[fallowfz]

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

 

[John Spencer]

To get just the date part you could use

CDate(Format(Left("24APR2008:14:12:17",9),"@@ @@@ @@@@"))

 

[Ken Sheridan]

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]

John, Ken - thanks for the help. And I should have clarified, all I
needed from the original field was the date and not the time.

I used the CDate(Format...) in a query and it worked sucessfully! I
had tried Format(CDate...) before with no luck.

I then tried to use the same in an update query, but all I saw was an
empty column. Would there be any reason this function would not work
as an update? Below is the SQL for the update query...

UPDATE MyTable SET MyTable.NewDateField = CDate(Format(Left
([TextField],9),"@@ @@@ @@@@"));

I set the formatting of the NewDateField to "GeneralDate"

I want to update the existing table with the NewDateField so I can
create totals by month, running totals by month, etc. Is there
another way to accomplish this...creating a query using the CDate
(Format...) query and the main table? (never done that before)

Thanks,

-Zack

 

[Ken Sheridan]

Zack:

Are there any Nulls in the TextField column? Try:

UPDATE MyTable
SET MyTable.NewDateField = CDate(Format(Left([TextField],9),"@@ @@@ @@@@"))
WHERE [TextField] IS NOT NULL;

NewDateField must be of Date/Time data type of course. The format is
irrelevant as date/time values are actually stored as a 64 bit floating point
number. You can format the column however you wish, and use different
formats in different places, e.g. you might format it in short date in a form
for data entry purposes and in long date in a report. The underlying value
is the same in each case.

Ken Sheridan
Stafford, England

 

[John W. Vinson]

John, Ken - thanks for the help. And I should have clarified, all I
needed from the original field was the date and not the time.

I used the CDate(Format...) in a query and it worked sucessfully! I
had tried Format(CDate...) before with no luck.

I then tried to use the same in an update query, but all I saw was an
empty column. Would there be any reason this function would not work
as an update? Below is the SQL for the update query...

UPDATE MyTable SET MyTable.NewDateField = CDate(Format(Left
([TextField],9),"@@ @@@ @@@@"));

I set the formatting of the NewDateField to "GeneralDate"

I want to update the existing table with the NewDateField so I can
create totals by month, running totals by month, etc. Is there
another way to accomplish this...creating a query using the CDate
(Format...) query and the main table? (never done that before)

Thanks,

-Zack

If you just open the Update query as a datasheet, you'll see the value before
the query is run (blank, in this case). You need to actually execute the query
by clicking the ! icon, and then look in the table directly, or open a form
based on the table.

 

[fallowfz]

Ken, John,

Thanks for the help. I hadn't actually executed (!) the query as John
pionted out...just viewing it in Design View. Fear of commitment i
guess.

Worked fine after that.

Thanks again,


-Zack