Date Defaults Updates to 30 December 1899

[Trevor G]

I am at a loss, on a form when changes are made I have a field that records the date when amendments are made, for some reason although it retains the date like 24 Febraury 2010 once you close the form and reopen it, it defaults this date field to 30 December 1899.

In the table it is a date/time field with a long date format.

Behind any control when it needs to update the field it is set to =Now()

Has anyone come across this before?

Trevor

 

[MSAccessRookie]

What exactly are you trying to do? Dates are either null or not. If a Date is Null then there is no value. If a Date is set to 0 then it will be interpreted as having a value of December 30, 1899. Check the Properties of the Field on your Form and the Column behind the Field. I suspect that you somehow have a default value of 0.

 

[Trevor G]

Thank you I have found that they had set a defualt to 0. I can now recitify this. I wonder why Microsoft has it set to 30 december 1899 though, this isn't a question to be answered as not all Microsoft Applications are logical such as Time in Excel, to Time in Access and Time in Word.

Thank you for pointing me in the right direction.

Trevor

 

[MSAccessRookie]

Thank you I have found that they had set a defualt to 0. I can now recitify this. I wonder why Microsoft has it set to 30 december 1899 though, this isn't a question to be answered as not all Microsoft Applications are logical such as Time in Excel, to Time in Access and Time in Word.

Thank you for pointing me in the right direction.

Trevor

No problems here. I was glad to assist. I just remembered that this issue was something that came up a while back, so the resolution was easy as long as the symptoms were the same.

I have no idea as to why the default value for Date(0) was selected to be 30 December 1899, perhaps there is someone who visits the forum that can enlighten us about the default value. I would be interested in knowing as well.

 

[ChrisO]

From this article by Eric Lippert


“And finally, why 30 December 1899? Why not, say, 31 December 1899, or 1 January 1900 as the zero day? Actually, it turns out that this is to work around a bug in Lotus 1-2-3! The details are lost in the mists of time, but apparently Lotus 1-2-3 used this date format but their devs forgot that 1900 was not a leap year. Microsoft fixed this bug by moving day one back one day.”

 

[SOS]

Thanks Chris!

From this article by Eric Lippert


“And finally, why 30 December 1899? Why not, say, 31 December 1899, or 1 January 1900 as the zero day? Actually, it turns out that this is to work around a bug in Lotus 1-2-3! The details are lost in the mists of time, but apparently Lotus 1-2-3 used this date format but their devs forgot that 1900 was not a leap year. Microsoft fixed this bug by moving day one back one day.”

Ooh, I love history. Thanks for that.

 

[ChrisO]

I have forgotten where I read it, and don’t know if it’s true, but it seems that originally Lotus 1-2-3 only used two digits to store the year. Normally a year is a leap year if it is wholly divisible by 4. However, a year which is also a turn of century year also needs to be wholly divisible by 400 and 1900 is not. In any case, if Lotus was not storing 4 digits then there would have been no way to calculate century years.

But I don’t know if that is true and hope I’m not starting another urban myth.

 

[Galaxiom]

How bizarre. That is an interesting article by Eric Lippert.

I have never worked with dates before the 20th century. It didn't occur to me that only the integer section of the numeric representation counted backwards prior to the zero date. The decimal section quite separately represents time as a positive just like it does after the zero date.

So taking the shortcut of simply adding or subtracting date/time values instead of using DateDiff would not work prior to the zero date.

I also noticed that any moment on 30/12/1899 will only display as a time in a table regardless of the format property of the field.

 

[ChrisO]

Yes the difference between Date/Times can be a little tricky to calculate directly on the double numbers. When both arguments are positive or when both arguments are negative there is no problem. But because the fractional day (Time) is always positive an allowance of one whole day needs to be made when the signs of the days differ.

Apples - Apples = Apples.
Oranges - Oranges = Oranges.
Apples - Oranges = One fruit wrong.