Change data type to date

[Heidestrand]

Hello,

I have an imported table from Excel with three columns I want to change the data type of. To do that I use the code

CurrentDb.Execute "ALTER TABLE [tblOrders] ALTER COLUMN [MaterialID] DOUBLE"

But now I want to change one column to date with this format: mm.yyyy
and the other one dd.mm.yyyy.
Right now it is the text format, but I need the date for both.
Can I use my code for this or do I need more code than that?

Thanks in advance for your help!

 

[Minty]

Your text field mm.yyyy won't get saved as a date if you alter the column type. I would also be careful with the second column as SQL interprets dates in mm.dd.yyyy format, so you could run into issues if it even tries to convert it.

You would need to run a function on them to update them to a genuine date field, as I don't think changing the field type would work.

 

[apr pillai]

Date Fields are actually of Data Type Double. Date & Time values are stored internally as Double precision number. Formatting will not change the stored value. mm.yyyy format string can be used to display the value in that format, date cannot be stored correctly in that format.

42262.0998726851851852 is equal to 15/09/2015 02:23:49

 

[Heidestrand]

Thank you for your answers. I figured out a solution that works better:
I'm going to prepare a table with my demanded field types and afterwards I will be copying the data from my imported table to the prepared one.

Only one question I have left: How can I insert from a text column into a date column? And from text to number (double)?

Edit:

I got it myself, I have to use CDate, CDbl and CInt when selecting the column