You are here:

Using MS Access/MS access - DateDiff function

Advertisement


Question
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)

Answer
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,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

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

Experience

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

Organizations
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

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.