Solved

Dlookup Date range

Posted on 2013-12-06
6
494 Views
Last Modified: 2013-12-23
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
Comment
Question by:SparkyP
6 Comments
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 167 total points
ID: 39700790
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
 
LVL 57
ID: 39700794
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
 

Author Comment

by:SparkyP
ID: 39700853
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 167 total points
ID: 39700859
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
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 166 total points
ID: 39700992
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
 

Author Closing Comment

by:SparkyP
ID: 39735616
Sorry for the delay, haven't mastered the code yet, but appreciate your input and expertise.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question