ACC: DateDiff() Function Returns Incorrect Value

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

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.