Solved

Dlookup Date range

Posted on 2013-12-06
6
485 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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 167 total points
Comment Utility
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 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 166 total points
Comment Utility
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
Comment Utility
Sorry for the delay, haven't mastered the code yet, but appreciate your input and expertise.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now