However, the assumption that Access can't handle time values to the millisecond is not entirely true. In fact, Access and Jet both can store milliseconds because Double, the underlying data type of date/time, has a resolution one thousand times larger than date/time's smallest increment of one second. However, manipulating time values with millisecond precision in Access requires some crafty programming. You need the help of a couple of API calls, which you wrap in functions that mimic the standard timing functions in Access. This article explains how to apply this technique, which will enable you to create, retrieve, and measure time values down to the millisecond. What's in the Demo?
Because this technique requires a great deal of code, this article does not outline every single step for recreating the demo. Doing so would just be too impractical. For your convenience, you can download demo .mdb and .accdb files in the accompanying source code instead of creating the example yourself. If you want only the code, both code modules are also included in the download.
The demo database files contain the following components:

• A table (tblTimeMsec) for storing date/time values with a resolution to the millisecond: You can work with any table of time values you like, as long as they contain the appropriate precision. This table simply helps demonstrate the code you'll need when applying this technique to your own database.
• A module (basTimeDemo) that contains a sub procedure named FillTime(): This procedure creates and enters time values into tblTimeMsec. These values include millisecond values. This procedure is for demonstration purposes only, but you can customize it to fit your needs. In addition, it uses Data Access Objects (DAO), so be sure to set a reference to that library.
• A module (basTime) with all the user-defined functions (UDFs) and API calls required to pull off this bit of timing magic: This is the only component that you need to add to your own database. You must use this module intact; don't change the names of any constants or calls. In addition, you might find several other user-defined functions useful for timing techniques beyond the one discussed here (See Sidebar 1. Useful UDFs for Other Timing Techniques).
• A simple query interface for displaying the results of the API calling expressions: This module contains quite a bit of code, much of which can be useful in ways beyond just this technique. Be sure to read the in-line comments for a helpful explanation of how everything works together.

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.