Calculating No. of Days excluding Public Holiday

[jjkeeper]

G'day, I'm currently working on an annual leave database for the company, so far so good, till they want the database to be able to identify public holidays and prevent reducing the employee's balance if the application overlaps with the holiday. I'm using access 2000. Here's what I have so far.

tblMainProfile - contains employee's profile
EmployeeID <== Pkey
Name
Date of Birth ..

tblLeaveApply - link to tblMainProfile by EmployeeID
LeaveID <=== Pkey
EmployeeID
DateFrom
DateTo
Half day (AM/PM)
Days <=== number of days between DateFrom and DateTo for that record
LeaveTypes
Year ..

tblLeaveEntitlement - link to tblLeaveApply by EmployeeID
Index <=== Pkey
EmployeeID
Year
LeaveType
LeaveEntitled

And then I have a query to calculate TotalDays, by

TotalDays: Sum(Nz([Days],0))

and DatesLeft, which is the leave balance,

DaysLeft: [LeaveEntitled]-[TotalDays].

This query is then used for sub-form1, linking EmployeeID with the Mainform.

The mainform shows that employee's profile (ID,name,department,nationality). Sub-form2 bounded directly to tblLeaveApply, is for employees to input in the dates and leave types to apply for leave. The order is:
1) employee chooses DateFrom and DateTo, AM/PM if halfday.
2) employee then clicks on a button to calculate the number of days between DateFrom and Dateto, and if AM/PM = Is Not Null, the Days-0.5. (calculations for the button is done in VB, on-click event).

If possible, can you recommend a design such that when the employee picks DateFrom, DateTo and AM/PM if applicable, and then clicks on the button to calculate number of days, the database checks through tblPH, and for every PH_Date that is within DateFrom and DateTo, then Days-1.

Well, I roughly know the logic, but this is more or less my first time actually doing a database, so I can't figure it out in terms of a database application. I tried using SELECT count function in query but it showed me 26, when I only have 2 PH_Dates in the table. So I'm a little puzzled. Plus, let's say that count function works, I can't figure out how to implement it into the form too.

Thanks,
Johnny

[JM420A]

As a thought, You could have a separate table that holds the date and title of your National Holidays. From there, you could do a query that searches for the dates where date not in (select * from holidaytable). My syntax is jacked up I'm sure, but it is more to give you an idea to start from.

hope that helps.

jm420a

[missinglinq]

JM420A has the right idea. The concept involved here is similar to that of calculating the working and non-holiday days between two dates. Here's a link to two routines for doing this. Studying them should help you in your task.

Welcome, JM420A and jjkeeper, to Bytes!

Linq ;0)>

[jjkeeper]

Thank you both!

@missinglinq, sorry but where's the link? I can't find.

[missinglinq]

Sorry! Apparently the editor was in Stealth Mode!

http://www.mvps.org/access/datetime/date0006.htm

The second example deals with holidays.

Linq ;0)>

[sudowtariq]

missinglinq Thanks for the code you've wrote. It was really helpfull.