UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Issue When Using Now(), Access 2007    
 
   
paulroy
post Apr 10 2019, 04:47 PM
Post#1



Posts: 79
Joined: 26-April 10



Hi,
I understand how Access stores dates as floating point numbers with the integer containing days and the decimal containing minutes. When I execute:
rst!DateLogged=Now()
can someone please explain to me how the system date gets interpreted with respect to month/day when it updates that field? Would this assignment look up the system formatting settings before updating that field? My guess is hopefully NO, but I am seeing some strange behavior where the month and day being reversed.
Thanks,
Paul


Go to the top of the page
 
MadPiet
post Apr 10 2019, 05:00 PM
Post#2



Posts: 3,002
Joined: 27-February 09



This "I am seeing some strange behavior where the month and day being reversed." sounds like your settings UK will use dd/mm/yyyy and US will use mm/dd/yyyy for their date formats.
Go to the top of the page
 
paulroy
post Apr 10 2019, 05:15 PM
Post#3



Posts: 79
Joined: 26-April 10



Here is my question another way... When I execute
rst!DateLogged=Now()
and read that date back for display with this Format Property on the Text Box yyyymmmdd ddd hh\:nn\:ss
the month and day flip when the day is below 13, above 13 the date is fine.
When I read the floating point number from the table, the date IS STORED WRONG which causes the flip.
This is a product that has been around for years and I have many customers, all in Canada, and I have never seen this until recently and I cannot figure why the date is stored into the table wrong??? Another point is that I have only seen this at one of my customers, and I cannot reproduce this in my development environment. I have been looking at this for a couple of days and at this point baffled.
Thanks,
Paul
Go to the top of the page
 
Jaiket
post Apr 10 2019, 05:41 PM
Post#4



Posts: 385
Joined: 3-May 17
From: France


Hello Paulroy,
I have no experience of this issue, but have read about it frequently.
It has been around for a while.
Allen Browne adressed the issue in some detail in 2008 - try a websearch for 'allen browne dates'.


--------------------
Life is too short to drink bad wine.
Go to the top of the page
 
Minty
post Apr 11 2019, 03:35 AM
Post#5



Posts: 270
Joined: 5-July 16



It's a very common problem.

In the user interface and on forms Access uses your local settings, however in VBA code and VBA generated queries it uses mm/dd/yyyy internally.
As suggested Allen Browne's resource is a excellent read with some suggested solutions http://allenbrowne.com/ser-36.html
Go to the top of the page
 
PaulBrand
post Apr 11 2019, 06:46 AM
Post#6



Posts: 1,695
Joined: 4-September 02
From: Oxford UK


Try this instead:

rst!DateLogged=Format(Now(),"yyyymmdd hh:nn:ss")

--------------------
Paul
Go to the top of the page
 
paulroy
post Apr 11 2019, 08:43 AM
Post#7



Posts: 79
Joined: 26-April 10



I have not tried this and yes that would be the next thing to try, thanks!
If this works, and that was the point of my original question, it would mean that Now() reads the date/time from Windows, converts it to month day and then converts that to Access's floating point notation and stores it.
If would be really awesome if some Access Guru could confirm this.
Thanks again!
Paul
Go to the top of the page
 
Minty
post Apr 11 2019, 09:03 AM
Post#8



Posts: 270
Joined: 5-July 16



I'm not a guru but Yes - From Microsoft;

Access stores date/time variables like floating point numbers where the integer part represents days since 30/12/1899 and the fractional part represents time of day.







Go to the top of the page
 
kfield7
post Apr 11 2019, 11:29 AM
Post#9



Posts: 947
Joined: 12-November 03
From: Iowa Lot


Re: "...and the decimal containing minutes."
Re: "... and the fractional part represents time of day."

At risk of sounding anal, the entire floating point number represents days.

The integer portion is whole days, the decimal is fraction of a day.
So a minute is (1 day/24 hr/day/60 min/hr) ~= 0.00069444444 days.
I use ~= (approximately) since seconds and minutes and hours bring factors of 3 resulting in repeating decimals.
12:01 am ~= 0.00069444444
3 days and 10 minutes ~= 3.0069444444
04/11/2019 11:12:25 ~=43,566.46696123
Day 0 time 0:00 for MS is 01/00/1900 0:00:00 (...days since 31/12/1899 0:00:00 or 30/12/1899 24:00:00)

You can easily confirm this by formatting some dates/values in Excel.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th April 2019 - 11:25 PM