Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-14-2019, 07:02 PM   #1
Porteño
Newly Registered User
 
Join Date: Sep 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Porteño is on a distinguished road
Format after change field datatype to date

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 by Porteño; 09-14-2019 at 07:32 PM.
Porteño is offline   Reply With Quote
Old 09-14-2019, 07:14 PM   #2
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,354
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Format after change field datatype to date

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 09-14-2019, 07:18 PM   #3
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,580
Thanks: 50
Thanked 1,050 Times in 1,031 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Format after change field datatype to date

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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-15-2019, 04:44 AM   #4
Porteño
Newly Registered User
 
Join Date: Sep 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Porteño is on a distinguished road
Re: Format after change field datatype to date

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
Attached Images
File Type: png odd date display.png (10.6 KB, 12 views)
Porteño is offline   Reply With Quote
Old 09-15-2019, 04:50 AM   #5
Porteño
Newly Registered User
 
Join Date: Sep 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Porteño is on a distinguished road
Re: Format after change field datatype to date

Quote:
Originally Posted by The_Doc_Man View Post
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.
Porteño is offline   Reply With Quote
Old 09-15-2019, 05:21 AM   #6
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,580
Thanks: 50
Thanked 1,050 Times in 1,031 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Format after change field datatype to date

Quote:
Originally Posted by Porteño View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-15-2019, 06:37 AM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,354
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Format after change field datatype to date

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 09-15-2019, 07:03 AM   #8
Porteño
Newly Registered User
 
Join Date: Sep 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Porteño is on a distinguished road
Re: Format after change field datatype to date

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
Code:
 CurrentDb.Tabledefs("table..").Fields("Startdatum").Properties("Format").Value = "Short Date"

Last edited by Porteño; 09-15-2019 at 07:22 AM.
Porteño is offline   Reply With Quote
Old 09-15-2019, 07:23 AM   #9
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,133
Thanks: 15
Thanked 1,570 Times in 1,492 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Format after change field datatype to date

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-15-2019, 07:32 AM   #10
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,580
Thanks: 50
Thanked 1,050 Times in 1,031 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Format after change field datatype to date

Quote:
Originally Posted by Porteño View Post
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
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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Format Field while retaining Datatype Spackle Queries 9 07-12-2018 02:08 PM
change date format Mike Hughes Queries 2 06-27-2011 11:17 AM
Question can i change the format of DATE() ?? Nokia N93 General 5 11-27-2010 11:57 AM
Change date format rio Queries 8 03-26-2009 05:45 PM
Date format change? kbrooks Tables 1 04-23-2003 07:19 AM




All times are GMT -8. The time now is 09:01 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World