Working with Dates in Microsoft Access: Functions and Queries

Dates are everywhere in business data — order dates, due dates, hire dates, expiration dates. And date handling in Access has more nuance than most developers expect. This guide covers everything you need to work with dates confidently in queries, forms, and VBA.

How Access Stores Dates

Access stores dates as floating-point numbers (the same as Excel). The integer part represents the number of days since December 30, 1899. The fractional part represents the time of day. This means:

• January 1, 2026 = 46023
• January 1, 2026 at noon = 46023.5

You never need to work with these numbers directly — Access's date functions handle the conversion — but understanding this explains why date arithmetic works the way it does.

Date Literals in Queries

In Access SQL, date literals are enclosed in pound signs:

WHERE OrderDate = #9/16/2026#
WHERE OrderDate >= #1/1/2026# AND OrderDate < #1/1/2027#

In VBA, use the same syntax or the CDate() function:

Dim d As Date
d = #9/16/2026#
d = CDate("September 16, 2026")

Essential Date Functions

Getting the Current Date and Time

Date()      ' Today's date only (no time component)
Now()       ' Current date and time
Time()      ' Current time only

Extracting Date Parts

Year(Date())          ' 2026
Month(Date())         ' 9 (September)
Day(Date())           ' 16
Weekday(Date())       ' 4 (Wednesday; 1=Sunday by default)
Hour(Now())           ' Current hour (0-23)
Minute(Now())         ' Current minute
DatePart("q", Date()) ' Quarter (1-4)
DatePart("ww", Date()) ' Week number of the year

Calculating Differences

DateDiff(interval, date1, date2) returns the number of interval units between two dates:

DateDiff("d", #1/1/2026#, #9/16/2026#)   ' 258 days
DateDiff("m", #1/1/2026#, #9/16/2026#)   ' 8 months
DateDiff("yyyy", #1/1/2020#, #9/16/2026#) ' 6 years
DateDiff("ww", #1/1/2026#, #9/16/2026#)  ' 37 weeks

Interval codes: "d" (days), "m" (months), "yyyy" (years), "q" (quarters), "ww" (weeks), "h" (hours), "n" (minutes), "s" (seconds)

Adding and Subtracting Dates

DateAdd(interval, number, date) adds a number of interval units to a date:

DateAdd("d", 30, Date())      ' 30 days from today
DateAdd("m", 3, #1/1/2026#)   ' April 1, 2026
DateAdd("yyyy", -1, Date())   ' One year ago
DateAdd("m", -6, Date())      ' Six months ago

First and Last Day of a Period

' First day of current month
DateSerial(Year(Date()), Month(Date()), 1)

' Last day of current month
DateSerial(Year(Date()), Month(Date()) + 1, 0)

' First day of current year
DateSerial(Year(Date()), 1, 1)

' First day of current quarter
DateSerial(Year(Date()), (DatePart("q", Date()) - 1) * 3 + 1, 1)

Date Formatting

The Format() function converts a date to a string in any format you specify:

Format(Date(), "Short Date")        ' 9/16/2026
Format(Date(), "Long Date")         ' Wednesday, September 16, 2026
Format(Date(), "Medium Date")       ' 16-Sep-26
Format(Date(), "mm/dd/yyyy")        ' 09/16/2026
Format(Date(), "yyyy-mm-dd")        ' 2026-09-16 (ISO 8601)
Format(Date(), "mmmm d, yyyy")      ' September 16, 2026
Format(Date(), "mmm yyyy")          ' Sep 2026
Format(Date(), "Q\Q yyyy")          ' Q3 2026
Format(Now(), "mm/dd/yyyy hh:nn AM/PM")  ' 09/16/2026 10:18 PM

Common Date Queries

Records from the Last 30 Days

WHERE OrderDate >= Date() - 30

Records from the Current Month

WHERE Year(OrderDate) = Year(Date()) AND Month(OrderDate) = Month(Date())

Records from the Current Year

WHERE Year(OrderDate) = Year(Date())

Records Overdue (past due date)

WHERE DueDate < Date() AND Status <> "Completed"

Age Calculation

SELECT FirstName, LastName, 
       DateDiff("yyyy", BirthDate, Date()) - 
       IIF(Format(BirthDate,"mmdd") > Format(Date(),"mmdd"), 1, 0) AS Age
FROM Employees

Fiscal Year Queries

If your fiscal year starts in a month other than January (e.g., July), adjust year calculations:

' Fiscal year starting July 1
Function FiscalYear(d As Date) As Integer
    If Month(d) >= 7 Then
        FiscalYear = Year(d)
    Else
        FiscalYear = Year(d) - 1
    End If
End Function

Common Date Pitfalls

Comparing dates with time components — Date() returns midnight (00:00:00). If your OrderDate field includes a time component, WHERE OrderDate = Date() will miss records from later in the day. Use WHERE OrderDate >= Date() AND OrderDate < Date() + 1 instead.

Null dates — DateDiff() and other functions return Null if either argument is Null. Use Nz() to substitute a default: DateDiff("d", Nz([StartDate], Date()), Date()).

Two-digit years — Access interprets two-digit years 00-29 as 2000-2029 and 30-99 as 1930-1999. Always use four-digit years in date literals to avoid ambiguity.

Regional date formats — Access uses the system locale for date input. A database shared between US and European users may interpret "01/02/2026" differently. Use ISO format (yyyy-mm-dd) in code to avoid ambiguity.

Conclusion

Date functions are among the most frequently used tools in Access development. DateDiff, DateAdd, DatePart, and Format cover the vast majority of real-world date calculations. Build a reference of the patterns you use most often, and date handling will become one of your strengths rather than a source of bugs.