Link to home
Start Free TrialLog in
Avatar of SparkyP
SparkyP

asked on

Dlookup Date range

I have just created a holiday request form, which has DateFrom and DateTo. Using Datediff I am able to create the total days. But obviously this does not take into account holidays and weekends.

I seem various post, which appear to be beyond me, so I have created a table (WorkingDates) of dates from today though to December 2020, excluding national holidays (UK) and weekends.

My intention is to have the datediff based on the Dlookup from the WorkDates table, which I am struggling to get to work.

As I am using SQl backend and Access frontend I was wondering if date format may be part of the issue. I am using UK date format in the form i.e. 06/12/2013

The data type in both the Holiday Request Table and Working Dates Table id Date.

Many Thanks in anticipation.
SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
My suggestion would be to use the first link, second solution, which assumes every day except weekends and those listed in the holidays table are working days.

That really lessens the calendar that you need to maintain.

Jim.
Avatar of SparkyP
SparkyP

ASKER

Jim

Many Thanks for rapid response. Please bear with me, but where would I place the trigger (and what trigger) to get the result in the field TotalDays.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SparkyP

ASKER

Sorry for the delay, haven't mastered the code yet, but appreciate your input and expertise.