The information in this article applies to:
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
MORE INFORMATIONStoring Date/Time DataMicrosoft Access stores the Date/Time data type as a double-precision, floating-point number (up to 15 decimal places). The integer portion of the double-precision number represents the date; the decimal portion represents the time.Valid date values range from -647,434 (January 1, 100 A.D.) to 2,958,465 (December 31, 9999 A.D.). A date value of 0 represents December 30, 1899. Microsoft Access stores dates prior to December 30, 1899 as negative numbers. Valid time values range from .0 (00:00:00) to .99999 (23:59:59). The numeric value represents a fraction of one day. You can convert the numeric value into hours, minutes, and seconds by multiplying the numeric value by 24. The following table illustrates how Microsoft Access stores Date/Time values:
To view how Microsoft Access stores Date/Time values as numbers, type the
following commands in the Debug window in Microsoft Access 97 and 7.0 (or
the Immediate window in Microsoft Access 2.0 or earlier), and note the
results:
To view the date and time of numeric values, type the following commands
in the Debug window (or the Immediate window), and note the results:
Formatting a Date/Time FieldYou can format a Date/Time value to display a date, a time, or both. When you use a date-only format, Microsoft Access stores a value of 0 for the time portion. When you use a time-only format, Microsoft Access stores a value of 0 for the date portion.The following table illustrates how Microsoft Access stores Date/Time values and how you can display those values using different formats:
NOTE: The default format for a Date/Time value is General Date. If a value
is date-only, then no time is displayed. If the value is time-only, then
no date is displayed.
Calculating Time DataBecause a time value is stored as a fraction of a 24-hour day, you may receive incorrect formatting results when you calculate time intervals greater than 24 hours. To work around this behavior, you can create a user-defined function to ensure that time intervals are formatted correctly.This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual. NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0 To calculate and format time intervals correctly, follow these steps:
Comparing Date DataBecause dates and times are stored together as double-precision numbers, you may receive unexpected results when you compare Date/Time data. For example, if you type the following expression in the Debug window (or the Immediate window in earlier versions), you receive a false (0) result even if today's date is 3/31/95:
The Now() function returns a double-precision number representing the
current date and time. However, the DateValue() function returns an
integer number representing the date but not a fractional time value. As a
result, Now() equals DateValue() only when Now() returns a time of
00:00:00 (12:00:00 A.M.).
To receive accurate results when you compare date values, use one of the functions below. To test each function, type it in the Debug window (or the Immediate window), substitute the current date for 3/31/95, and then press ENTER:
Comparing Time DataWhen you compare time values, you may receive inconsistent results because a time value is stored as the fractional portion of a double-precision, floating-point number. For example, if you type the following expression in the Immediate window, you receive a false (0) result even though the two time values look the same:NOTE: The time values that fail depend on the version of Visual Basic for Applications. Access 2.0: var1 = #2:00:00 PM#Access 95 and 97: var1 = #2:01:00 PM#When Microsoft Access converts a time value to a fraction, the calculated result may not be the exact equivalent of the time value. The small difference caused by the calculation is enough to produce a false (0) result when you compare a stored value to a constant value. To receive accurate results when you compare time values, use one of the methods below. To test each method, type it in the Immediate window, and then press ENTER:
REFERENCESFor more information about calculating time values, please see the following article in the Microsoft Knowledge Base: Q88657 ACC: Functions for Calculating and Displaying Date/Time ValuesFor more information about how to format Date/Time data types, type "formatting dates" in the Office Assistant, click Search, and then click to view "Format Property - Date/Time Data Type." Additional query words: lapsed time
Keywords : kbprg kbusage TblDatyp |
Last Reviewed: November 23, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |