I imported a table from Excel.
A filed containing value "01-09-2016" was set to short text datatype.
When I go to table->design View and change column to DATE/TIME, the value turns to 1/9/2016, great.
but, when I run this:
the value in this field turn to 42378
How do I make it look like a proper date programmatically?
EDIT:
I tried to run an update setting the same value in the field but it came out empty.
If I open the table and click on the value, then I can't leave the cell because is an invalid value.
This is pretty odd, doesn't it?
EDIT 2:
I run:
CurrentDb.Execute "UPDATE[table..] SET [Startdatum] = DATEADD('d',[Startdatum], '1899-12-30');"
And nothing changed! the same number!
but verified that this number is correct
SELECT DATEADD('d',42378,'1899-12-30')
results in 1/9/2016
A filed containing value "01-09-2016" was set to short text datatype.
When I go to table->design View and change column to DATE/TIME, the value turns to 1/9/2016, great.
but, when I run this:
Code:
CurrentDb.Execute "ALTER TABLE [mytable] ALTER COLUMN [Startdatum] DATETIME;"
the value in this field turn to 42378
How do I make it look like a proper date programmatically?
EDIT:
I tried to run an update setting the same value in the field but it came out empty.
If I open the table and click on the value, then I can't leave the cell because is an invalid value.
This is pretty odd, doesn't it?
EDIT 2:
I run:
CurrentDb.Execute "UPDATE[table..] SET [Startdatum] = DATEADD('d',[Startdatum], '1899-12-30');"
And nothing changed! the same number!
but verified that this number is correct
SELECT DATEADD('d',42378,'1899-12-30')
results in 1/9/2016
Last edited: