Microsoft KB Archive/109339

{|
 * width="100%"|

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