Microsoft KB Archive/109339

From BetaArchive Wiki

PRB: Incorrect Time Calculations in Years Prior to 1900

Article ID: Q109339
Creation Date: 02-JAN-1994
Revision Date: 19-SEP-1996 The information in this article applies to:

  • Microsoft Access versions 1.0 and 1.1

SYMPTOMS


Using the DateAdd() function to perform a time calculation on a Date/Time field that contains a date prior to the year 1900 returns an incorrect value. For example, the expression

   DateAdd("n",30,#January 1, 1776 9:00:00 AM#)

should add 30 minutes to the value, but instead returns:

   January 1, 1776 8:30AM

CAUSE

Microsoft Access stores dates as serialized numbers that represent the number of days from a reference date. The time component is represented by the digits to the right of the decimal point. Since Microsoft Access uses December 31, 1899 as the reference date, all dates prior to that date are stored as negative numbers. Microsoft Access correctly interprets larger negative whole numbers as earlier dates, but incorrectly interprets larger negative fractions as an earlier time.

RESOLUTION


Use an immediate if (IIf) function, or an If-Then-Else construction, in Access Basic to reverse the sign of the calculation. The following example demonstrates how to use the IIf() function to reverse the calculation's sign for a Date/Time field called MyDate.

NOTE: In the following example, an underscore (_) is used as a line- continuation character. Remove the underscore when re-creating example.

   IIf([MyDate]<#12/31/1899#, DateAdd("n",-30,[MyDate]),_
   DateAdd("n",30,[MyDate]))

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access versions 1.0 and 1.1. This problem no longer occurs in Microsoft Access version 2.0.

REFERENCES


Microsoft Access "Language Reference," version 1.0, pages 102-103



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

©1997 Microsoft Corporation. All rights reserved. Legal Notices.


Additional reference words: 1.00 1.10 query ab
KBCategory: kbusage
KBSubcategory: ExrOthr