Anniversary dates

[gmacg]

I have an Attendance database for employees. I have a table named tblActiveHourly that holds employee info such as:

EmployeeNumber
DateHired
Department

The Attendance table contains records of days absent with fields

EmployeeNumber
Date
Reason

Predefined reasons for absence are selected from a drop down box.

I have a query based on the attendance table and the tblActiveHourly with calculated fields that look at the DateHired and the current date and determines how many years the employee has worked and how many weeks vacation the employee has earned.

Now I would like to figure out how to look at the anniversary date and the current date to count the vacation days used from the attendance table. Here is what I came up with to determine the anniversary date and it works:

AnniversaryDate: DateValue(DatePart("m",[DateHired]) & "/" & DatePart("d",[DateHired]) & "/" & DatePart("yyyy",Now()))

It will give me the anniversary date, but I can't figure out how to restrict the records to dates between the Anniversary date and current date. Does that make sense?

I've been beating my head against this wall all day and all I'm getting for my efforts is a sore head. Can someone please, please, please help???

 

[Golom]

The simple answer is

WHERE [AttendanceDate] BETWEEN

DateValue(DatePart("m",[DateHired]) & "/" & DatePart("d",[DateHired]) & "/" & DatePart("yyyy",Now()))

And

Date()

However ...

the problem is, the current date may be before, on or after the anniversary date. What do you expect it to return if you run it on the anniversary date? Before the anniversary date? Do you want to use the last anniversary date that precedes the current date even if it is in the preceding year? Your calculation of anniversary date will always be in the current year.

And what do you do about someone hired on February 29th? Or is hiring forbidden on that day?

 

[gmacg]

Thanks, Golom. The questions that you asked are exactly what I am struggling with. I think I've got it figured out though. I can subtract the absence date from the anniversay date and base everything on whether the result is positive, negative, or zero....maybe. Any way that's the angle I'm working from for the moment.

As for February 29th....well....that's a whole nuther story...I like the idea of forbidding hiring on that day...but then, I guess if you forbid hiring on that day, you will also have to forbid vacations on that day....and birthdays on that day.....why don't we just forbid any transactions on that day....I think I'll just become a beach bum and forget the whole thing.