Convert Text field to Date field within existing database

21 Dec 2008 1:39 PM

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

21 Dec 2008 2:04 PM

John Spencer

To get just the date part you could use

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

21 Dec 2008 4:55 PM

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

Show quoteHide quote

"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
>

21 Dec 2008 9:52 PM

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

21 Dec 2008 11:15 PM

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

Show quoteHide quote

"fallowfz" wrote:

> 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
>
>

21 Dec 2008 11:34 PM

John W. Vinson

On Sun, 21 Dec 2008 13:52:09 -0800 (PST), fallowfz
<zack.fallowfi***@roche.com> wrote:

Show quoteHide quote

>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.

--

John W. Vinson [MVP]

22 Dec 2008 1:09 AM

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