A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Convert Text field to Date field within existing database



 
 
Thread Tools Display Modes
  #1  
Old December 21st, 2008, 02:39 PM posted to microsoft.public.access.gettingstarted
fallowfz
external usenet poster
 
Posts: 4
Default 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  
Old December 21st, 2008, 03:04 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 2,364
Default 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  
Old December 21st, 2008, 05:55 PM posted to microsoft.public.access.gettingstarted
Ken Sheridan
external usenet poster
 
Posts: 3,433
Default 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  
Old December 21st, 2008, 10:52 PM posted to microsoft.public.access.gettingstarted
fallowfz
external usenet poster
 
Posts: 4
Default 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  
Old December 22nd, 2008, 12:15 AM posted to microsoft.public.access.gettingstarted
Ken Sheridan
external usenet poster
 
Posts: 3,433
Default 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  
Old December 22nd, 2008, 12:34 AM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old December 22nd, 2008, 02:09 AM posted to microsoft.public.access.gettingstarted
fallowfz
external usenet poster
 
Posts: 4
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:25 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.