ACC: DateDiff() Function Returns Incorrect Value (104973)


The information in this article applies to:

  • Microsoft Access 1.0
  • Microsoft Access 1.1
  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
This article was previously published under Q104973
Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

When you use the DateDiff() function on a large interval where the time periods are specified in minutes or seconds, the result is not correct.

CAUSE

The DateDiff() function returns a long integer. If the result is greater than the allowed range for long integers (-2,147,483,648 to 2,147,483,647), Microsoft Access truncates the value and returns the truncated value.

MORE INFORMATION

Steps to Reproduce Behavior


  1. Create a new module. Enter the following in the module's Debug window (or Immediate window in versions 1.x and 2.0), and then press ENTER:

    ? DateDiff("n",#12-31-100#,#12-31-9999#)

    Note that the returned value, 911,403,103 minutes, is truncated because the actual value exceeds 2,147,483,647.
  2. Enter the following calculation, which is equivalent to the function above, and then press ENTER: 
          x=#12-31-100#
      y=#12-31-9999#
      ? (y-x)*24*60

    Note that the returned value, 5,206,370,400 minutes, is correct. Note that Microsoft Access uses double-precision real numbers when doing immediate calculations.
Modification Type:MajorLast Reviewed:5/6/2003
Keywords:kbprb kbusage KB104973
©2002 Microsoft Corporation. All rights reserved.