Format after change field datatype to date (1 Viewer)

Porteño

New member
Local time
Yesterday, 20:42
Joined
Sep 14, 2019
Messages
6
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:
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:

The_Doc_Man

Happy Retired Curmudgeon
Local time
Yesterday, 18:42
Joined
Feb 28, 2001
Messages
16,838
First, 42378 might be the right date anyway. Dates are shown as timeline distances from a reference date and that number is about right for this date. So the question is, what was the format by which you were viewing that date?

You see, date/time fields in Access are actually a typecast (or if you prefer, alternate interpretation) of a DOUBLE. Depending on how you ask the question, you might see the DOUBLE and you might see the date/time in some format.

Also, is that a native Access table or an SQL Server back-end table? SQL server has more than one date type so we need to clarify the context of your question.
 

theDBguy

I’m here to help
Local time
Yesterday, 16:42
Joined
Oct 29, 2018
Messages
10,216
Hi. I’m just guessing, but that’s probably a proper date already. Behind the scenes, Access stores date values as a number. What we know or see as dates is merely a formatted version of the number. Just to verify, go back to design view, after you did the ALTER TABLE and select a Format for the field.

Edit: Oops, I swear Doc’s response wasn’t there when I started. Sorry for the duplicate information.
 

Porteño

New member
Local time
Yesterday, 20:42
Joined
Sep 14, 2019
Messages
6
Hi, it is plain Access.

And yes, the number is correct. It is not shown as DATE but shown correctly in the calendar when opening the table.

Is there some metadata I can change for proper display?

See table open and design view attached
 

Attachments

Porteño

New member
Local time
Yesterday, 20:42
Joined
Sep 14, 2019
Messages
6
So the question is, what was the format by which you were viewing that date?

Also, is that a native Access table or an SQL Server back-end table?
Hi, I just double-clicked on table in object panel (All Access Objects, tables).

It just Access imported from Excel and converted by ALTER TABLE, if I converted by design view, everything looks normal.
 

theDBguy

I’m here to help
Local time
Yesterday, 16:42
Joined
Oct 29, 2018
Messages
10,216
Hi, it is plain Access.

And yes, the number is correct. It is not shown as DATE but shown correctly in the calendar when opening the table.

Is there some metadata I can change for proper display?

See table open and design view attached
Hi. What the image you posted doesn’t show is what’s in the Format property of the field. If it’s empty, try selecting one from the dropdown.
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Yesterday, 18:42
Joined
Feb 28, 2001
Messages
16,838
I'm theDBguy on this one. Check for an empty FORMAT property. Note that doing an ALTER TABLE doesn't fill in the format unless you explicitly tell it the format, whereas the manual alteration of formats carries some "baggage" with it that changes the format property. Think of ALTER TABLE as the programmed - but dumbed-down - way of changing table properties. It makes no assumptions. Sometimes, you WANT assumptions.
 

Porteño

New member
Local time
Yesterday, 20:42
Joined
Sep 14, 2019
Messages
6
Yes, thanks, my intuition was in that direction, as I titled this post.

Adding format in design view solved it.

But, how I change that metadata programmatically? this doen's cut it:

Code:
CurrentDb.TableDefs(tablename).Fields("Startdatum").Format
EDIT:

I found it :D
Code:
 CurrentDb.Tabledefs("table..").Fields("Startdatum").Properties("Format").Value = "Short Date"
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:42
Joined
Feb 19, 2002
Messages
28,774
In a relational database, tables are not generally added on the fly. It sounds like you are importing speadsheets and making them new tables. I would reconsider this as a design. Access is not a spreadsheet and if you try to use it as one, you will be sorely disappointed. It will probably be better for you to append new data to an existing table. That way you can use a single set of forms and queries rather than having to make new ones for each table. Your queries can include criteria so that you can select data by date range, or filename (assuming you store this in the table when you import the file), etc. When you use a control on a form, the value of the criteria is variable but the query never changes so you have ONE query and pass it an argument at runtime.
 

theDBguy

I’m here to help
Local time
Yesterday, 16:42
Joined
Oct 29, 2018
Messages
10,216
Yes, thanks, my intuition was in that direction, as I titled this post.

Adding format in design view solved it.

But, how I change that metadata programmatically? this doen's cut it:

Code:
CurrentDb.TableDefs(tablename).Fields("Startdatum").Format
EDIT:

I found it :D
Code:
 CurrentDb.Tabledefs("table..").Fields("Startdatum").Properties("Format").Value = "Short Date"
Hi. Glad to hear you got it sorted out, but you might consider what Pat was saying. Good luck with your project.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom