Examples of MS Access DateDiff function used in query and VBA code

Examples of MS Access DateDiff function used in query and VBA code

The date format in the examples below is determined by the Short date format that was set in in Control Panel's Regional Options.

For detailed description of DateDiff function, refer to this page.

Example queries below can be pasted to MS Access' query design and run as is.

Please note:

The firstdayofweek argument takes default value Sunday and firstweekofyear takes the week in which the first of January occurs.

This query returns 4 years as its interval is Year.

select DateDiff("yyyy",#25/11/2006#,#01/01/2010#)

This query returns 13 quarters as its interval is Quarter.
```
select DateDiff("q",#25/11/2006#,#01/01/2010#)
```
This query returns 38 months as its interval is Month.
```
select DateDiff("m",#25/11/2006#,#01/01/2010#)
```
This query returns 1,133 days as its interval is Day.
```
select DateDiff("d",#25/11/2006#,#01/01/2010#)
```
This query returns 162 weeks as its interval is Week.
```
select DateDiff("ww",#25/11/2006#,#01/01/2010#)
```

This query returns 1 hour as its interval is Hour.

select DateDiff("h",#25/11/2006 8:11:50 AM#,#25/11/2006 9:11:50 AM#)

This query returns 60 minutes as its interval is Minute.

select DateDiff("n",#25/11/2006 8:11:50 AM#,#25/11/2006 9:11:50 AM#)

This query returns 3,600 seconds as its interval is Second.

select DateDiff("s",#25/11/2006 8:11:50 AM#,#25/11/2006 9:11:50 AM#)

More examples:

DateDiff function can be used in SQL queries WHERE, GROUP BY, HAVING, SORT BY...

1. Get the average days that orders were fullfilled based on the number of days between order date and shipment date.

The result is 8.49 days where ROUND function is used to round the value to 2 decimal points.

select ROUND(AVG(DateDiff("d",OrderDate, ShippedDate)),2) as Average_Delivery_Days from Orders

2. VBA code example

Private Sub Command0_Click()
    Dim dteTimeStart As Date
    Dim dteTimeEnd As Date
    
    dteTimeStart = Now()
    
    '' Add 5 hours to the start time
    dteTimeEnd = DateAdd("h", 5, dteTimeStart)

    '' Calculate the number of minutes between start and end time
    MsgBox "Number of minutes is: " & DateDiff("n", dteTimeStart, dteTimeEnd)
End Sub

Number of minutes difference

Other Recent Articles from the MS Access category:

1.	MS Access DateDiff function
2.	How to find out your computer name and username by VBA
3.	Examples of MS Access DatePart function
4.	MS Access DatePart function
5.	Examples of MS Access DateAdd function
6.	MS Access DateAdd function
7.	IIF function basics - the CASE statement of MS Access
8.	MS Access Date Expression
9.	Solved: MS Access error "The text is too long to be edited"
10.	Create MS Access Combo Box essential properties by VBA code
11.	Create MS Access Combo Box essential properties manually
12.	How to do text search in MS Access programmatically
13.	Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
14.	How to easily get a list of field names in MS Access
15.	How to count distinct records in MS Access
16.	How to do transaction based processing in MS Access
17.	How to open a document (local/network file or web page) from MS Access
18.	How to use ADOX to create unique composite index - the VBA approach
19.	How to do cross-table update queries in MS Access - the right way
20.	Three efficient ways to get the number of records by using VBA
21.	How to create a composite unique index (not as a primary key) in MS Access
22.	Use VBA to get the correct number of records in a Recordset object
23.	Disable Access Prompt when a record is changed, table deleted, or action queries run
24.	How to hide and unhide a MS Access object
25.	How to return multiple values from a VBA function (Part 3)
26.	How to return multiple values from a VBA function (Part 2)
27.	How to return multiple values from a VBA function (Part 1)
28.	Three ways to programmatically duplicate a table in MS Access by VBA
29.	Create a DLL by CSharp or VB.Net for VBA
30.	How to correctly reference and call a DLL
31.	How to register a C# or VB.Net DLL
32.	Email address validation by Regular Expressions using VBA
33.	Fix MS Access error: Query must have at least one destination field
34.	How to unselect radio buttons in MS Access after it has been selected
35.	How to Change Query Timeout Value for MS Access SQL Queries
36.	What is Northwind Traders database

	Custom Search