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,003
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: 271
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: 271
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
 
gemmathehusky
post Today, 01:36 PM
Post#10


UtterAccess VIP
Posts: 4,446
Joined: 5-June 07
From: UK


I generally use rst!datelogged = format(now, "long date") to get around UK/US date format issues, but you can't do that when there is a time involved.
I am told it doesn't work in all domains, but I am only in the UK, and I know this will also work in the US.

With a time element, I have been fine with simply rst!datelogged = cdbl(now()), because then I am storing the actual number representing the date, and not allowing Access to get the days and months confused.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
isladogs
post Today, 03:19 PM
Post#11



Posts: 1,307
Joined: 4-June 18
From: Somerset, UK


Just to be equally anal about dates, the zero date for Access was 30 Dec 1899 00:00
Confusingly for Excel it was 31 Dec 1899 as kfield stated above.

The date is stored correctly as a floating point number.
However, whilst queries will correctly handle dates in dd/mm/yyyy format, SQL will not reliably do so.
Any date up to 12th will be reversed because the date still makes sense as mm/dd/yyyy.
However anything from 13th onwards is unambiguous and will be read correctly.

Having said that, SQL's attempts to read dates can still do wrong.
For example, I've entered an invalid date in the immediate window

?cdate(#29/2/17#)
17/02/2029
This post has been edited by isladogs: Today, 03:22 PM

--------------------
Go to the top of the page
 
cheekybuddha
post Today, 03:42 PM
Post#12


UtterAccess VIP
Posts: 11,056
Joined: 6-December 03
From: Telegraph Hill


Colin,

Very odd to call CDate() on a date!

CODE
?CDate(#28/2/17#)
17/02/2028
?CDate("28/2/17")
28/02/2017
?CDate("29/2/17")
17/02/2029     '  29th Feb 2017 doesn't exist!

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th April 2019 - 03:43 PM