MS Access: DateValue Function 5 Examples of Microsoft Access Date Functions
Monday, March 4, 2019

MS Access: DateValue Function 5 Examples of Microsoft Access Date Functions




Office

Office


Cart

Sign in

  • Apps

    • Outlook
    • OneDrive
    • Word
    • Excel
    • PowerPoint
    • OneNote
    • SharePoint
    • Microsoft Teams
    • Yammer
    • Access
  • Install
  • Account
  • Training
  • Admin

Format the date and time field in Access

Access 2010
More…
Less

When you use the Date/Time data type in Microsoft Access, you have the option to keep the default formats, apply a predefined format, or create a custom format. This article briefly explains the Date/Time data type, how to add a date and time field to a table and how you can apply a predefined or custom format.

What do you want to do?

Overview of date and time formats

Add a Date/Time field to a table

Format the Date/Time field

Restrict date and time entry format by using an input mask

Additional information

Overview of date and time formats

Access automatically displays the date and time in the General Date and Long Time formats. The dates appear as, mm/dd/yyyy in the U.S. and as, dd/mm/yyyy outside the U.S. were mm is the month, dd is the day, and yyyy is the year. The time is displayed as, hh:mm:ss AM/PM, where hh is the hour, mm is minutes, and ss is seconds but you can display the date and time in several formats.

These automatic formats for dates and times vary depending on the geographic location specified in the Microsoft Windows Regional and Language Options setting on your computer. For example, in Europe and many parts of Asia, depending on your location, you might see the date and time as 28.11.2018 12:07:12 PM or 28/11/2018 12:07:12 PM. In the United States, you would see 11/28/2018 12:07:12 PM. It is possible to change these automatic formats by using custom display formats. The display format that you select will not affect how the data is entered or how Access stores that data. For example, you can enter a date in a European format such as 28.11.2018, and have the table, form, or report display the value as 11/28/2018.

Access automatically provides a certain level of data validation related to date and time formatting. For example, if you enter an invalid date, such as 32.11.2018, a message appears and you would have the option of entering a new value or converting the field from the Date/Time data type to the Text data type. Valid date values range from -657,434 (January 1, 100 A.D.) to 2,958,465 (December 31, 9999 A.D.). Valid time values range from .0 to .9999, or 23:59:59.

For more information on the date/time data type, see the article Introduction to data types and field properties .

Top of Page

Add a Date/Time field to a table

  1. Open the table where you want to insert the Date/Time field in Datasheet view.

  2. Click the down arrow next to Click to Add column and select Date & Time from the drop down field list.

  3. Rename your field and save the table.

To add dates to the field, just click the Date Picker or calendar icon and do one of the following:

  • To enter the current date, click Today.

  • To select a day in the current month, click the date.

  • To select a different month and day, use the forward or back buttons.

Top of Page

Format the Date/Time field

Access provides several predefined formats for the date and time data, but if these formats do not meet your needs you can specify a custom format. The default format is the General Date format — m/dd/yyyy h:mm:ss AM/PM.

Apply a predefined format

  1. If you don’t already have a date and time field, add the field. Open the table in Datasheet view, click the down arrow next to Click to Add column, and select Date & Time from the drop down field list.

  2. Right click the table header and switch to the Design view.

  3. In the upper section of the design grid, select the Date/Time field that you want to format.

  4. In the Field Properties section, click the arrow in the Format property box, and select a format from the drop-down list.

    date and time properties

  5. After you select a format, the Property Update Options smart tag appears, and lets you to apply your new format to any other table fields and form controls that would logically inherit it. To apply your changes throughout the database, click the smart tag, and then click Update Format everywhere Field Name is used. In this case, Field Name is the name of your Date/Time field.

  6. To apply your changes to the entire database, when the Update Properties dialog box appears and displays the forms and other objects that will inherit the new format. Click Yes.

  7. Save your changes and switch to Datasheet view to see whether the format meets your needs.

Apply a custom format

When you create a custom format, you enter various characters in the Format property of a table field. The characters consist of placeholders (such as 0 and #), separators (such as periods and commas), literal characters, and colors. Custom formats for the Date/Time fields can contain two sections — one for the date and another for time — and you separate the sections with a space.

When you apply a custom format to the Date/Time field, you can combine different formats by having two sections, one for the date and another for the time. In such an instance, you would separate the sections with a space. For example, you can combine the General Date and Long Time formats as follows: m/dd/yyyy h:mm:ss. To apply a custom date or time format, complete the following steps:

  1. In the Navigation Pane, right-click the table that you want to work with, and then click Design View.

  2. In the upper section of the design grid, select the Date/Time field you want to format.

  3. In the lower section, click the Format property box, and then enter your custom format by using characters from the Placeholders and separators for a custom format table.

  4. After you enter a format, the Property Update Options smart tag appears and lets you apply the format to any other table fields and form controls that would logically inherit it. To apply your changes throughout the database, click the smart tag, and then click Update Format everywhere Field Name is used. In this case, Field Name is the name of your Date/Time field.

  5. If you choose to apply your changes to the entire database, the Update Properties dialog box appears and displays the forms and other objects that will inherit the new format. Click Yes to apply the format.

  6. Save your changes, and then switch to Datasheet view to see whether the format meets your needs.

Top of Page

Restrict date and time entry format by using an input mask

When you want to force users to enter date and time in a specific format, use an input mask. For example, if you applied an input mask to enter dates in the European format, someone entering data in your database will not be able to enter dates in other formats. Input masks can be applied to fields in tables, query result sets, and to controls on forms and reports.

You can define an input mask in one format but set a different display format. For example, you can have an input mask require the date to be entered as 2018.04.15, but have the date display as, 15-April-2018.

Add an input mask

  1. Select the Date/Time field, and then in the lower section of the design grid, on the General tab, click the Input Mask property.

  2. The Input Mask Wizard opens and guides you through selecting an Input Mask format.

  3. To apply the format, click Yes, and then click Save.

Top of Page

Additional information

Predefined formats and examples

Format

Description

Example

General Date

(Default) Displays date values as numbers and time values as hours, minutes, and seconds followed by AM or PM. For both types of values, Access uses the date and time separators specified in your Windows regional settings. If the value does not have a time component, Access displays only the date. If the value has no date component, Access displays only the time.

06/30/2018 10:10:42 AM

Long Date

Displays only date values, as specified by the Long Date format in your Windows regional settings.

Monday, August 27, 2018

Medium Date

Displays the date as dd/mmm/yy, but uses the date separator specified in your Windows regional settings.

27/Aug/18
27-Aug-18

Short Date

Displays date values, as specified by the Short Date format in your Windows regional settings.

8/27/2018
8-27-2018

Long Time

Displays hours, minutes, and seconds followed by AM or PM. Access uses the separator specified in the Time setting in your Windows regional settings.

10:10:42 AM

Medium Time

Displays hours and minutes followed by AM or PM. Access uses the separator specified in the Time setting in your Windows regional settings.

10:10 AM

Short Time

Displays only hours and minutes. Access uses the separator specified in the Time setting in your Windows regional settings.

10:10

Top of Page

Custom format placeholders and separators

Use any combination of the following components to build a custom format. For example, to display the week of the year and day of the week, type ww/w.

Important:  Custom formats that are inconsistent with the date/time settings specified in Windows regional settings are ignored. For more information on Windows regional settings, see Windows Help.

Date format components

Format

Description

d  

Day of the month in one or two numeric digits, as needed (1 to 31).

dd  

Day of the month in two numeric digits (01 to 31).

ddd 

  First three letters of the weekday (Sun to Sat).

dddd 

  Full name of the weekday (Sunday to Saturday).

w 

  Day of the week (1 to 7).

ww 

Week of the year (1 to 53).

m  

Month of the year in one or two numeric digits, as needed (1 to 12).

mm  

Month of the year in two numeric digits (01 to 12).

mmm 

  First three letters of the month (Jan to Dec).

mmmm  

Full name of the month (January to December).

q  

The quarter of the year (1 to 4).

y  

Number of the day of the year (1 to 366).

yy 

  Last two digits of the year (01 to 99).

yyyy

   Full year (0100 to 9999).

Time format components

Format

De
s
cription

H

Hour in one or two digits, as needed (0 to 23).

hh  

Hour in two digits (00 to 23).

N

Minute in one or two digits, as needed (0 to 59).

nn  

Minute in two digits (00 to 59).

s  

Second in one or two digits, as needed (0 to 59).

ss  

Second in two digits (00 to 59).

Clock format components

Format

Description

AM/PM  

Twelve-hour clock with the uppercase letters “AM” or “PM,” as appropriate. For example, 9:34PM.

am/pm  

Twelve-hour clock with the lowercase letters “am” or “pm,” as appropriate. For example, 9:34pm.

A/P  

Twelve-hour clock with the uppercase letter “A” or “P,” as appropriate. For example, 9:34P.

a/p  

Twelve-hour clock with the lowercase letter “a” or “p,” as appropriate. For example, 9:34p.

AMPM 

Twelve-hour clock with the appropriate morning/afternoon designator as defined in the regional settings of Windows

Separator components

Note:  Separators are set in the regional settings of Windows.

Any short string of characters, enclosed in quotation marks (“”) is a custom separator. The quotation marks are not displayed. For example, “,” displays a comma.

:   Time separator. For example, hh:mm

/   Date separator. For example, mmm/yyyy

Top of Page

Facebook

LinkedIn

Email

Expand your Office skills

Explore training

Get new features first

Join Office Insiders

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

Contact Support

×

Courses

x
  • Software Development
  • IT Ops
  • Data Professional
  • Information & Cyber Security
  • Browse all technologies
  • Courses
  • Python
  • JavaScript
  • Java
  • C#
  • Web Development
  • Mobile Development
  • View all courses >
  • Paths
  • JavaScript
    JavaScript

    • 9 Courses
    • 21 Hours
  • AngularJS
    AngularJS

    • 14 Courses
    • 55 Hours
  • Java
    Java

    • 13 Courses
    • 48 Hours
  • View all paths >
  • Assessments
  • Angular
    Angular

    • Get your Skill IQ
  • Node.js
    C#

    • Get your Skill IQ
  • Python
    Python

    • Get your Skill IQ
  • View all assessments >
  • Courses
  • IT Certifications
  • IT Networking
  • Security
  • Database Administration
  • Virtualization
  • Servers
  • View all courses >
  • Paths
  • Fundamentals of IT Operations
    Fundamentals of IT Operations

    • 29 Courses
    • 87 Hours
  • CompTIA A+
    CompTIA A+

    • 11 Courses
    • 40 Hours
  • CompTIA Network+
    CompTIA Network+

    • 6 Courses
    • 26 Hours
  • View all paths >
  • Assessments
  • Planning Microsoft Azure Infrastructure
    Planning Microsoft Azure Infrastructure

    • Get your Skill IQ
  • Networking Fundamentals
    Networking Fundamentals

    • Get your Skill IQ
  • Understanding Dev Ops
    Understanding Dev Ops

    • Get your Skill IQ
  • View all assessments >
  • Courses
  • Big Data
  • Business Intelligence
  • SQL
  • Tableau
  • Oracle
  • SQL Server
  • View all courses >
  • Paths
  • Using the Scrum Framework
    Using the Scrum Framework

    • 7 Courses
    • 13 Hours
  • CompTIA Storage+
    CompTIA Storage+

    • 3 Courses
    • 11 Hours
  • Upgrading Your Technology Career
    Upgrading Your Technology Career

    • 28 Courses
    • 51 Hours
  • View all paths >
  • Assessments
  • C#
    C#

    • Get your Skill IQ
  • MySQL
    MySQL

    • Get your Skill IQ
  • Android
    Android

    • Get your Skill IQ
  • View all assessments >
  • Courses
  • Cyber Security
  • Security Certifications
  • Penetration Testing
  • Security Auditing
  • Malware Analysis
  • Security Fundamentals
  • View all courses >
  • Paths
  • Incident Handler (ECIH and GCIH Prep)
    Incident Handler (ECIH and GCIH Prep)

    • 3 Courses
    • 12 Hours
  • CySA+ (CompTIA Cybersecurity Analyst) CS0-001
    CySA+ (CompTIA Cybersecurity Analyst) CS0-001

    • 7 Courses
    • 28 Hours
  • SSCP
    SSCP

    • 8 Courses
    • 25 Hours
  • View all paths >
  • Assessments
  • Security Fundamentals
    Security Fundamentals

    • Get your Skill IQ
  • Security for Hackers and Developers
    Security for Hackers and Developers

    • Get your Skill IQ
  • View all assessments >
  • Software Development
    Software Development
  • IT Ops
    IT Ops
  • Creative Professional
    Creative Professional
  • Creative Professional
    Data Professional
  • Architecture & Construction
    Architecture & Construction
  • Manufacturing & Design
    Manufacturing & Design
  • Business Professional
    Business Professional
  • Information & Cyber Security
    Information & Cyber Security
x
Business

x
Team
Team
Help your team keep up with the pace of change.

Built for teams >

Team
Enterprise
Helping thousands of organizations transform at scale.

Solutions at scale >

See how Pluralsight can bridge the technology skills gap for your entire team. Start a team trial >
Personal
LIVE 2019
    • Get started
    • Log in
    • Personal
    • Team
    • Enterprise
    • LIVE 2018
    • Contact
    • Categories
    • Software Development
    • IT Ops
    • Creative Professional
    • Data Professional
    • Architecture & Construction
    • Manufacturing & Design
    • Business Professional
    • Information & Cyber Security
Get started
Log in

5 Examples of Microsoft Access Date Functions

– select the contributor at the end of the page –
Test your skills. Learn something new. Get help. Repeat. Start a FREE 10-Day trial
Microsoft Access date functions are a powerful way to retrieve and analyze data in your Access databases, but very few users know about these functions or how to use them. In this article, I describe a few ways you can leverage Microsoft Access date functions in your Access 2007 and Access 2010 databases.

If you have never used Microsoft Access date functions, take a look at this quick Beginner’s Guide to Microsoft Access Built-in Date Functions that I recently created and shared on our Office YouTube channel.

1. Automatically Insert Today’s Date (Beginner Level)

If you find yourself constantly entering whatever today’s date is in your Access forms or tables, you are wasting valuable time. Access can do that for you, and you don’t have to be an Access expert to do so!

There are a few ways to have Access insert today’s date, but the simplest way is add a simple Date() function to the Default Value property of your table.

Let’s say we have a database that tracks applicants and case workers, and when cases are assigned, signed out, and closed. Following this example, let’s say we would like to automate the date for when a case is received.

  1. Locate the table containing the date field you wish to modify and open it to Design View (right-click the table and choose Design View). In our example, we would need to open the table that stores our case information.Automatically Insert Date in Access
  2. Once in Design View, select the field you wish to modify. In our example, it will be the DateReceived Field.
  3. In the Field Properties area, on the General Tab, locate the property for “Default Value” and type Date(). Adding Access Date Function
  4. Next, switch over to your table view and verify that for new records, today’s date is automatically populated in the field. Access date automatically populated

Once this property is set for your field, users can skip past this field when doing data entry or modify the date as necessary. Alternatively, if the field is time-sensitive as well, you can also use the Now() function in place of the Date() function to store both the date and time information.

2. Prevent Data Entry for Dates After Today’s Date (Intermediate)

Date functions are also useful for controlling what dates are allowed to be entered into a given Date/Time field. Let’s say we had a sales table where we only want users to enter a transaction occurring on or before today’s date. We can achieve this level of control by writing an expression in the field’s Validation Rule property box inside the table.

  1. Open the table containing the field you want to control to its Design View (right-click table, and click Design View).
  2. Select the field you wish to change. In our example, the field is our “Invoice Date” field.
  3. In the bottom portion of the screen, under Table Properties, click inside the Validation Rule property box, and enter the following:<=Now()This will ensure that users enter a date occurring on or before today’s date. Or, in other words, the date entered must be less than or equal to now. Access Validation Rule
  4. Next, type “Transaction must occur on or before today’s date” inside the Validation Text property box. That way, if a user enters a date occurring after whatever “now” is, the user will be alerted that the transaction date “must occur on or before today’s date.”Access error messageNote: This message will only appear if the data entered does not meet the validation requirements as written in your expression. If the date entered is valid (does occur on or before today’s date), the record will simply be saved and the user will not receive any warnings.

For more examples of validation rules and expressions, please visit the Microsoft Help Topic Create a validation rule to validate data in a field.

3. Store the Date a Record was Last Modified or Updated (Advanced)

The following method is only available for Microsoft Access 2010 databases, as this will use a new feature called a Data Macro to automatically update a LastUpdated field to the current date and time whenever a record is modified.

For this example, let’s say we want to track when an employee’s contact information was last updated and build a query to locate employees whose information has not been updated recently (say, in the last year).

First, I will need to add a field to my table and name the field “LastUpdated.” This field will be used to store the date/time information gathered whenever a record is updated (you can use Microsoft’s Northwind sample database to practice, if you like).

  1. Open the employee’s table to Design View (or any table that you wish to track when records were last modified) and add a new Date/Time field named “LastUpdated.” Access Date Function: LastUpdated
  2. Save the table.
  3. In the Table Tools | Design tab, click on “Create Data Macros” button and select Before Change. Access Data Macros: Before Change This will open up the Macro Designer for the Before Change property, meaning any actions you program here will be evaluated and applied before a record is saved to the database. And since we want to log when a record was last updated, we want to apply our timestamp or date function to the LastUpdated field just before the record is saved.
  4. Click in the “Add New Action” drop menu, and select the action “SetField” from the list. That will add the action, plus two required arguments for the action, Name and Value.
  5. In the Name box, write the reference to the field you would like updated before the change of the record. In our example, we would type “LastUpdated.”
  6. In the Value box, write an expression for how you’d like the field changed. In our example, we’ll need to type Now(). Access Macro Tools
  7. Click Save in the Macro Tools | Design tab, and then click Close.The Macro should now be applied to our table.
  8. Save the table.

To test the data macro, just locate a record in the table and edit something. Upon moving off the record, Access should not only save changes to the record but enter the current date and time into the LastUpdated field.

Test Data Macro in Access

Over time, as more data is edited and entered, I can query this field to check to see which employees’ information might be outdated, and request that those employees check and update their information as necessary.

4. Filter by Date Functions (Intermediate)

In addition to using date functions for controlling and streamlining data entry, Microsoft Access date functions really shine when used as query criteria to filter or limit query results by date or date range. In the previous example, we created a field to track when employee records were last updated. Now, we can build a query to filter results to show only records that haven’t been updated within the last six months.

  1. Create a query using the Access Query Designer (Create Tab > click Query Design).That will open a new query to Design View and open a Show Table dialog box.
  2. Next, select the table you wish to query, click Add, and then close the Show Table dialog box. Show Table in Access
  3. You can then add fields to your query by double-clicking or dragging the desired field down to your QBE grid. In our example, we want to display the First Name, Last Name, E-mail Address, and LastUpdated fields. Access LastUpdated Field
  4. After all fields have been added, underneath the LastUpdated field, in the first criteria box, we will want to add an expression to show records last updated over 6 months ago. To accomplish this, we can use the DateDiff function to calculate the difference, in months, between the LastUpdated date and whatever now is, and then show only those records that are over 6 months old. So, our expression would look something like this:DateDiff(“m”,[LastUpdated],Now())>6LastUpdated

To explain what you are seeing, the DateDiff Function has three required function arguments: interval, date1, and date2. Here, the interval is “m” because we want to calculate in total months elapsed in order to find records older than 6 months old. Date1 and Date2 represent the two dates for which we want to calculate the difference. In our example, Date1 is the information stored in the LastUpdated field.

For Date2, however, since we want to save and reuse the query, we want to reference the Now() function, to calculate using the system date and time. So, our DateDiff function is simply calculating the difference between when the field was last updated and right now. To limit our query results down to show only those records older than 6 months, we can then tack on a greater than symbol and the number six after the DateDiff function.

Upon saving and rerunning this query, you might notice that Access will display our written expression a bit differently:

New LastUpdated

This is yet another way of expressing the same thing.

5. Locate Today’s Birthdays (Intermediate)

In addition to locating records by a difference between two dates, you can also query your database based on part of a date. And if we want to locate contacts or employees whose birthday is today, that is exactly what we will need to do — match part of the date (month and day) with today’s month and day. There are several ways to accomplish this in Access using date functions.

First, we could use the DatePart function to extract both the month and day from a field that stores employees’ birthdays. The DatePart function looks like this:

DatePart(«interval», «date», «firstdayofweek», «firstweekofyear»)

Interval is a required string expression representing the interval of time you want returned.

Date is another required variant representing the value or date you want to evaluate.

The last two arguments are optional, and are not necessary for our example.

So an expression to extract the month from a date field would look something like this:

DatePart(“m”,[Birthday])

“m” stand for month

[Birthday] is the name of the field that stores employees’ birthdates.

To extract the day, the only thing we have to change about the expression is the time interval.

DatePart(“d”,[Birthday])

Now, putting everything together, we’d need to write an expression that sets those extracted values equal to whatever today’s month and day are. For that, we could use the same DatePart function to extract the day and month and change the Date argument to the Now() function. So, the complete query criteria would look like this:

Access Locate Birthday

In other words, the month of Birthday is equal to the month of right now, and the day of Birthday is equal to the day of now.

This is just one way you could write this expression. Another way is to use the Month and Day functions instead of the DatePart function. Here is a video showing how to do that.

I hope this will get you to a good start with Microsoft Access Date Functions.

Ready to test your skills in Access? See how they stack up with this assessment from Smarterer . Start this Access test now.

 

Get our content first. In your inbox.


1229



Contributor

Heather Ackmann

is an accomplished instructor who has taught over 4,000 students at the high school, college, and adult levels. Specializing in computer applications, writing, and literature, she holds a degree in English and Secondary Education, an Illinois Type 09 Initial Teaching Certificate, and is a Microsoft Certified Master for Office 2003. While her energetic and easy-to-follow style is ideal for beginners to computer applications, her knowledge, thoroughness, and foresight in potential problems will satisfy the more proficient user.

We use cookies to make interactions with our websites and services easy and meaningful. For more information about the cookies we use or to find out how you can disable cookies, click here .

Accept cookies and close this message
Disable cookies

You have disabled cookies and are browsing in private mode. For the best possible experience on our website, please accept cookies. For additional details please read our privacy policy .

Accept cookies and close this message