Holiday Year Problem - Access 97

[Luther1978]

Hi all,
I'm putting together an Access DB for Employees.
I have created a table to hold employees booked/taken holidays, simply a start date and a duration of time booked in working days.

I have produced a query that creates totals from the holiday's booked and takes this value from each employee's entitlement to give the amout of days each employee has left. Great, all works fine except my employees all have a different start to their holiday year.

This causes a major problem at a time like now when next year's holidays have been booked, as I have employees with negative values for holiday left due to next years holidays being included in the query.

I have a field to hold an employees holiday year commencement date. dd/mm/yy. I would like to be able to drop the year field if I can, though using masks it can be ignored. Anyone have any ideas as to what calculation I can build to automatically query each employee on their particular holiday Year?

Thanks in advance

Martin King


My Table Structures if of Any help.

Personal Details Holiday
---------------- --------------
Employee ID Holiday Ref No
Leave Year Commencement Employee ID
Holiday Left Holiday Date
Holiday Entitlement Duration

Relationship:
(Employee ID) 1----To--------Many (Employee ID)

 

[Luther1978]

I have manaaged to solve my own problem, wasn't aware that Access/VB had a command DatePart. Easy really.

Many Thanks

Martin King