RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


MS Access Can Handle Millisecond Time Values--Really : Page 2

MS Access can retrieve and measure time with millisecond precision, but only with the help of a few well-known API calls and several user-defined functions.

Extending Time in Access
In Access, the built-in timing function with the highest precision is Timer(), but the resolution is only 1/64 second, or about 16 milliseconds. You need a custom timer function to achieve a higher resolution. An essential trick that makes this possible is the conversion between native and linear time. When using linear time, a linear change in negative time will represent a linear change in the numeric value.Native time, on the other hand, follows a saw tooth shift (see Figure 1).

Figure 1. Linear and Native Comparison: This Excel chart shows the saw tooth shift between native and linear time values around the date of numeric zero, December 30, 1899.

As you can see from Figure 1, for positive times, native time is identical to linear time and it is continuous. Negative native times follow a non-continuous sequence where only the values for full days match the linear time. Note that the native time shifts by a numeric value of 2 when passing midnight. This behavior leaves a range between numeric -1 and 0 for which no native time representation exists; this is utilized by TimeSerial() to hold a negative time in the range for hours before midnight.

Running the Time-Extending Demo
The first thing you need to run the demo is some time values to store and manipulate. For that, run the FillTime() sub procedure shown in Listing 1. Table 1 defines the three optional arguments for FillTime().

Table 1. FillTime() Arguments
Argument Explanation
intDateSet An Integer value that specifies whether to use the minimum, maximum, or a specific date
datBase A date/time value that specifies the date for the date/time values
intStep An Integer value that determines resolution

You could easily adapt FillTime() for your own use. For this demo, FillTime() is simply a tool that provides example time values. It isn't critical to the actual precision-resolving technique; it is provided here merely for your convenience.

You can run Listing 1 from the Immediate window to add more records using any of the following syntax rules:

  • Call FillTime(): Inserts 1,000 records for 1899-12-30 00:00:00
  • Call FillTime(intDateSet): Inserts 1,000 records as follows:
    • -1: 100-01-01 00:00:00
    • 0: 1899-12-30 00:00:00
    • 1: 9999-12-31 23:59:59
  • Call FillTime(0, datBase): Inserts 1,000 records for the specified date passed by datBase, when intDateSet equals 0
  • Call FillTime(intDateSet, datBase, intStep): Inserts the number of records specified by intStep for minimum, maximum, or a specified date

Figure 2. The qdyTimeMsecShow Query Interface: The query returns several values that demonstrate time to the millisecond precision in Access.

FillTime() uses a DAO recordset object to append up to 1,000 records to an existing table named tblTimeMsec. Each record includes a time value with one second, including milliseconds between 0 and 999.

The module that contains all the timing magic is basTime. This is the only component in the demo file that you need to apply to your own databases to take advantage of this timing technique.

The query qdyTimeMsecShow serves as the demo's principal interface. As you can see in Figure 2, the query returns several formatted and/or rounded values—the results of the many calls to user-defined and API functions. Table 2 lists and explains the query expressions that return these values.

Table 2. Query Expressions
Field Name Expression Explanation
DateTimeMs N/A The native date/time values using the default display format of Access
SortValue DateSort([DateTimeMs]) DateSort(), a UDF, returns a Double for correct sorting, including the milliseconds.
DateFull StrDateFull([DateTimeMs]) StrDateFull(), a UDF, returns a date/time value including milliseconds, as a formatted string.
CDouble CDbl([DateTimeMs]) CDbl() converts the date/time value to a Double to display the true numeric value so you can actually see the millisecond resolution.
RoundSecSQL Fix([DateTimeMs]*24*60*60)/(24*60*60) Rounds off milliseconds
MsecSQL (([DateTimeMs]-Fix([DateTimeMs]))*24*60*60*1000)*Sgn([DateTimeMs]) Mod 1000 Returns an Integer by extracting the millisecond part
MsecVBA Millisecond([DateTimeMs]) Returns an Integer by extracting the millisecond part using Millisecond()
SecondDec SecondMsec([DateTimeMs]) SecondMsec, a UDF, displays seconds and milliseconds as a decimal number
SecondStd Second([DateTimeMs]) Displays seconds and milliseconds rounded to the nearest second using Second()

If you study the query's output, you'll see the following:

  • DateTimeMS shows the rounded date/time values to the nearest second.
  • SortValue shows how DateSort() sorts correctly, even for negative values.
  • CDouble converts the date/time values to their numeric equivalents.
  • DateFull displays formatted date/time strings so you can see how they'll look, say in a report.
  • RoundSecSQL shows the rounded date/time values including milliseconds to the second using native SQL functions.
  • SecondStd shows the seconds rounded by Access.
  • The remaining fields demonstrate different components you can display or extract.

The Msec() function is the core function. If you study it carefully, you'll see that you have to initialize it to synchronize the milliseconds to the real time. This will cause a delay of 1-10 milliseconds the first time you run the timing function. Thus, if you plan to run millisecond timing (not needed for storing and calculation) in your application, you should make one call to Msec() at an early stage where the delay won't matter.

Precision Timing
It's easy to jump to conclusions where Access and timing precision are concerned. If you can't use milliseconds in your expressions, it doesn't seem to matter that Access can store them—or at least you might think it doesn't matter. But if you're willing to make a few API calls, you'll find that Access can store and manipulate milliseconds in both time values and expressions.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date