Syntax Error In ALTER TABLE Command

[Guest]

I would like to change the data type of a column of integers to date/time
format. I'm using this command in a MS Access 2003 macro RunSQL -> ALTER
TABLE ExpTable MODIFY Activation_DT DATETIME. I have chosen No for the Use
Transaction parameter.

I've "Googled" the Internet for a resolution, but am having some
difficulties finding my way out of this error. Would anyone have a
suggestion?

Many thanks,
Kenny Vick
(e-mail address removed)

 

[Dirk Goldgar]

In

I would like to change the data type of a column of integers to
date/time format. I'm using this command in a MS Access 2003 macro
RunSQL -> ALTER TABLE ExpTable MODIFY Activation_DT DATETIME. I have
chosen No for the Use Transaction parameter.

I've "Googled" the Internet for a resolution, but am having some
difficulties finding my way out of this error. Would anyone have a
suggestion?

Did you look in the Access help, in the book "Microsoft Jet SQL
Reference"? The syntax of the statement would be:

ALTER TABLE ExpTable ALTER COLUMN Activation_DT DATETIME

However, if you have integers already entered in this column, you may
not get them converted the way you expect. The integer value 0
corresponds to December 31, 1899; 1 corresponds to January 1, 1900; and
so on.

 

[Guest]

Dirk ...

Thank you VERY much. This did the trip. Since I'm not a developer by
profession, but a novice hacker this made my life much easier. Would you
know how to make it so that I only get the date and not the time?

Thanks,
Kenny

 

[Dirk Goldgar]

In

Dirk ...

Thank you VERY much. This did the trip.

You're welcome.

Would
you know how to make it so that I only get the date and not the time?

I'm not entirely sure what you mean. A date/time field *always* has
both date and time components; that's the nature of the data type.
There's no difference between #11/15/2007# and #11/15/2007 12:00:00
AM# -- the exact same value is stored.

You may choose how the field is *displayed* by setting its Format
property. There you can decide whether to display the time portion or
not.

If you have existing data that contains a non-zero time component and
you want to remove that and leave only the date, you can execute an
update query that sets the field to DateValue(the field); e.g.,

UPDATE MyTable SET MyDateField = DateValue(MyDateField)