• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

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.
0
SparkyP
Asked:
SparkyP
3 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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)PresidentCommented:
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now