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.
SparkyPAsked:
Who is Participating?
 
Vadim RappCommented:
Here's what we are using:
CREATE TABLE [WorkDays] (
	[date] [datetime] NOT NULL PRIMARY KEY,
	[workday] [bit] NOT NULL CONSTRAINT [DF__Temporary__workd__6FE99F9F] DEFAULT (1),
	[workday_index] [int] NULL 
) ON [PRIMARY]
GO

/* populate the above table, set [workday] = 0 for the holidays */

/* the following script reindexes the table; run whenever the holidays are changed. workday_index speeds up calculations in the functions that use it */
update workdays set workday_index=null

declare c cursor local fast_forward for
select [date],workday from workdays  order by 1
declare @i int, @d datetime, @w bit
select @i=0
open c
fetch c into @d,@w
while @@fetch_status=0 begin
	if @w=1 set @i=@i+1
	update workdays set workday_index=@i where [date]=@d
	fetch c into @d,@w
end
close c
deallocate c

go


/* 2 functions, equivalent of datediff and dateadd */
create FUNCTION dbo.WorkDateDiffDays(@d1 datetime,@d2 datetime)
RETURNS INT  AS  
BEGIN 

set @d1=cast(floor(cast(@d1 as float)) as datetime)
set @d2=cast(floor(cast(@d2 as float)) as datetime)

declare @r int

if @d1=@d2
 select @r=0

if @d1<@d2
select @r=count(*)-1
from workdays w
where [date] between @d1 and @d2
and workday=1

if @d1>@d2
select @r=1-count(*)
from workdays w
where [date] between @d2 and @d1
and workday=1

return @r
END

go

CREATE function WorkDateAdd(@Date as datetime, @Num as integer) returns datetime begin

declare @d datetime
select @d=w2.[date]
from workdays w2 join workdays w1
on w2.workday=1
and w2.workday_index-w1.workday_index=@num
where w1.[date]=cast(floor(cast(@date as float)  ) as datetime)

return @d
end

Open in new window


Then you can use it like

select dbo.workdatediffdays('1/1/2013','2/5/2013')
select dbo.workdateadd('6/25/2013',15)
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Your not going to be able to use DateDiff(), or Dlookup() directly to get this done.  Instead, you need to use a custom function to calculate the days.

So two sets of functions you may be interested in:

Calculate Number of Working Days
http://access.mvps.org/access/datetime/date0006.htm

Doing WorkDay Math in VBA
http://access.mvps.org/access/datetime/date0012.htm

and yes, Access/JET expects dates to be in the mm/dd/yyyy format.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
SparkyPAuthor Commented:
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.
0
 
Gustav BrockCIOCommented:
Yes, reverse the table to hold holidays only. Then you can use the function from the link below and a controlsource like this:

=ISO_WorkdayDiff([DateFrom],[DateTo], True)

Calculate workdays

Of course, adjust these to the names of your table and field:

  Const cstrTableHoliday    As String = "tblHoliday"
  ' Name of date field in holiday table.
  Const cstrFieldHoliday    As String = "HolidayDate"

The date field should be of data type DateTime.

/gustav
0
 
SparkyPAuthor Commented:
Sorry for the delay, haven't mastered the code yet, but appreciate your input and expertise.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.