Access 2007 Question on Formulas inside a query
Posted on 2011-10-31
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?