You are here:

Using MS Access/MS access - DateDiff function


I am trying to create a query/form where I can plug in two different dates and get the year, month, and days to populate into their own individual fields. I have been able to get the year and month to display accurately, but I have no idea how to get the days to account for the different number of days depending on the month. Here is what I have so far:

Field1 - Start Date

Field2 - End Date

Field3 - Years: DateDiff("yyyy",[Start Date],[End Date])

Field4 - Months: DateDiff("m",[Start Date],[End Date])-([years]*12)-1

Field5 - Days: DateDiff("d",[Start Date],[End Date])-([Years]*365)-([Months]*30)

First question is what do you plan on doing with the information.

From what you want I would think this is a better option for a form. So you have a form with 5 textbox controls:

txtStart: Start Date
txtEnd: End Date
txtYears: =DateDiff("yyyy",[txtStart],[txtEnd])
txtMonths: =DateDiff("m",DateAdd("yyyy",[txtYears],[txtEnd])
txtDays: =DateDiff("d",DateAdd("m",[txtYears]*12+[txtMonths]),[txtEnd])

The idea is that you adjust the start date for each DateDiff). You were close, but I think this will work better.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.