If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Convert Text field to Date field within existing database
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 |
#2
|
|||
|
|||
Convert Text field to Date field within existing database
To get just the date part you could use
CDate(Format(Left("24APR2008:14:12:17",9),"@@ @@@ @@@@")) |
#3
|
|||
|
|||
Convert Text field to Date field within existing database
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 |
#4
|
|||
|
|||
Convert Text field to Date field within existing database
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 |
#5
|
|||
|
|||
Convert Text field to Date field within existing database
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 "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 |
#6
|
|||
|
|||
Convert Text field to Date field within existing database
On Sun, 21 Dec 2008 13:52:09 -0800 (PST), 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 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] |
#7
|
|||
|
|||
Convert Text field to Date field within existing database
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 |
Thread Tools | |
Display Modes | |
|
|