About this lesson
Date/Time is an important, but problematic, data type. Recognize Date/Time functions and formats and how to enter them into fields.
Download this lesson’s related exercise files.Services_16_start.accdb
1.3 MB Services_16_DateTime_DataType.accdb
1.3 MB Analyzer_150603_Connect_Reports_s4p.zip
Date/Time Data Type
To enter the current date, press (Ctrl-; )
To enter the current time, press (Ctrl-Shift-; or Ctrl-: )
Date and Time
To enter date and time in a field, enter date, space, time.
Date/Time is an important, but problematic, data type. Depending on where you are, dates and times are displayed differently, according to your Windows Region Settings.
Internally, a Date/Time is stored as a number, which is why the number sign is used to delimit date/time values. Date and Time both measure the same thing: the passing of time.
A Date/Time value shows several pieces of information:
The earliest date Access can reference is January 1, 100, which is -657,434
The maximum date Access can store is 31 Dec 9999, which is 2,958,465.
The Time part of a Date/Time is stored as a fraction. Noon is really just halfway through a day.
Date and Time both measure the same thing, just in different terms.
Be careful when you are comparing dates as Access may have a time as well, even though a Format keeps it from showing.
Now() is a function to return the current date and time.
Date() is a function to return the current date.
To return the current time, there is no built-in function. You can use this equation:
TimeValue( Now() )
To construct a date from numbers for year, month, and day, use:
DateSerial( «year», «month», «day» )
For example, this is 25 December, 2016:
DateSerial( 2016, 12, 25 )
To construct a time from numbers for hour, minute, and second, use:
TimeSerial( «hour», «minute», «second» )
For example, this is 4:30 pm (notice you can also use expressions for the arguments:
TimeSerial( 12+4, 30, 0 )
To see the number that is actually being stored for a date/time, here is one of the formats you can use:
You can get lots of information from a date/time by formatting it. For instance, you can format a date/time to show you month names, day names, week numbers, and quarters.
The following is from Help.
Format Property - Date/Time Data Type ___________________________________________________________________________________
The following table shows the predefined Format property settings for the Date/Time data type.
You can create custom date and time formats by using the following symbols.
Custom formats are displayed according to the settings specified in the regional settings of Windows. Custom formats inconsistent with the settings specified in the regional settings of Windows are ignored.
If you want to add a comma or other separator to a custom format, enclose the separator in quotation marks as follows: mmm d", "yyyy.
Enter Date and Time into a field
- Type the Date
- Type a space
- Type the Time
Change Date and Time Format for Windows
- Press Windows-X to go to the Control Panel
- Choose Control Panel from the shortcut menu
- Choose Region.
- Change settings on the Formats tab
Lesson notes are only available for subscribers.