Access 2007 Question on Formulas inside a  query

Posted on 2011-10-31
Medium Priority
Last Modified: 2012-05-12
Hi Everyone,

I have a query that I am trying to find the difference between dates and determine the number of days and months.

My query has a field called DateTo that comes in from the database.  this date will be 12/31/2011 for every employee 1 through 910.
I also have a field called LVProgDate which is the Date a Person started to work.  This date will be different for each employee.
I have to format the LVPROGDATE because it comes in from the mainfraim as 2011/12/14 for eaxmple so I have a field inside my query with this Formula:
Expr1: CDate(Mid([LPROGDATE],6,2) & "/" & Right([LPROGDATE],2) & "/" & Left([LPROGDATE],4))

I have another field called TotalDays with this formula:  Total Days: DateDiff("d",[Expr1],[DateTo])

I have another field called TotalMonths with this formula:  Total Months: [Total Days]/365*12

The problem I am having is the Total Days are off sometimes with the system which makes my Total Months be off.

How can I write the formulas to be right on the money, because I need the right Total Months figure in order to figure an employee's longevity pay for the year which is determined by how many months an employee has worked to date?


Question by:jjc9809
  • 4
  • 2
  • 2
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37059387
"Off" by how much"?
Under what circumstances?
Can you provide a specific example...?

This is tricky with DateDiff, because of rounding.
You also have to sometimes consider the Time as well (A "Date" will default to approx Midnight) to get an accurate calculation of "Days"
LVL 44

Expert Comment

ID: 37059406
Confirm LvProgDate from the mainframe is a string?  If so, and your system short date format is dd/mm/yyyyy, then CDate("2011/12/14") should produce a date 12/14/2011.  You can test this in the Immediate Pane (Alt+F11) by typing:

? Date()
10/31/2011 '  this confirms the short date format

? CDate("2011/12/14")
12/14/2011   'ie.  you do not need to tear the string apart and re-assemble it in the manner you showed.

Confirm for a LvProgDate of 07/01/2011 the span is 5 months 30 days?

Author Comment

ID: 37059588
You are right CDate(LVPROGDATE) produces the 12/14/2011.  

Does the DateDiff work effectively with the formula I showed you?

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 44

Accepted Solution

GRayL earned 2000 total points
ID: 37059678
The DateDiff() function will provide a correct result when using two valid dates.  You can test your formula by going to the Immediate Pane (type Alt+F11) and type in the following:

LVPROGDATE = "2011/12/14"

? CDate(Mid([LVROGDATE],6,2) & "/" & Right([LVROGDATE],2) & "/" & Left([LVROGDATE],4))
12/14/2011  '<--  this is what the formula should produce.

Note the spelling mistake in the variable name - you have LPPROGDATE instead of LVPROGDATE.

What is your answer to the time span I asked in my previous post?
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37063030

What I was referring to was the confusion many people may feel when using DateDiff.
For ex:
DateDiff("M", #1/30/2011#, #2/2/2011#)
...will yield "1" ...and many people will wonder why.
Because there is technically only a 3 "Day" difference in actual "Days", yet in "Months" this difference is 1.

The key here is to use the exact "Interval" that you need.

It looks like Ray have you covered here, so I'll let you continue with him to avoid confusion.



Author Closing Comment

ID: 37077224
I am going to ask another question that is related to this.  The date problem is solved, but I have another issue I got to solve now.
LVL 44

Expert Comment

ID: 37079312
If this and the new question are related, be sure to refer this this question in your new one.
LVL 44

Expert Comment

ID: 37079314
BTW, thanks, glad to help.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question