Query Question

[Secret Squirrel]

I have the following query being used to track vacation time for my
employees. The problem I'm going to have is at the end of the year when the
year changes. I need to append and then update some of the info from this
query for the new year. But the problem is if I don't do it until the new
year starts then the data won't be accurate. So it would need to be done
before the year ends since I use the "Date()" function within my query a few
times. Is there a way I can replace this function with a value from a table
called "tblYear". This way the data will stay the same until the value in
that table is changed to the new calendar year. This will cover me if for
some reason the data is not appended and updated before the calendar year
ends. This is sort of like changing the fiscal year in an accounting system.
How can I do this?


Here is the SQL code:

SELECT tblEmployees.ID, tblEmployees.LastName, tblEmployees.FirstName,
tblEmployees.StartDate, Format([StartDate],"mm") AS StartMonth,
DateDiff("m",tblEmployees.StartDate,Date()) AS MonthsAccrued,
Round(DateDiff("m",tblEmployees.StartDate,Date())/12,2) AS YearsAccrued,
IIf(DateDiff("m",tblEmployees.StartDate,Date())<=12,1,0) AS YearCount,
Year(Date())-Year([StartDate]) AS CalcYear,
Switch([StartMonth]=1,24,[StartMonth]=2,20,[StartMonth]=3,16,[StartMonth]=4,12,[StartMonth]=5,8,[StartMonth]=6,4,[StartMonth]>=7,0)
AS [Vacation Hours Earned], [Vacation Hours Earned]/8 AS DaysAccrued,
tblEmployees.VacationOverrides, tblEmployees.VacationCarryover,
[DaysAccrued]+[VacationOverrides]+[VacationCarryover] AS TotalDays,
qryVacationTimeUsed.DaysUsed AS VDaysUsed,
IIf(nnz([VDaysUsed])=0,[TotalDays],[TotalDays]-[VDaysUsed]) AS NetDaysAvail
FROM tblEmployees LEFT JOIN qryVacationTimeUsed ON tblEmployees.ID =
qryVacationTimeUsed.EmpID
WHERE (((Year(Date())-Year([StartDate]))<1))
ORDER BY tblEmployees.LastName;

 

[Wayne-I-M]

The Date() formula contain the full date including the year - you could
either add a caculated column format YY or add the Date() to your
Format([StartDate],"mm") and change it to MM YY. Or just extract the year
section.

It seems you already have the data you need you just need to slight alter
the query to be alble to use it.

It would be a good idea to spend some time adding the YY to your data so you
can use it to not only work out the yearly holidays - but it could also be
used to compare like for like or for historic reports, etc