Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dlookup Date range

Posted on 2013-12-06
6
Medium Priority
?
507 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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 501 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 58
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 501 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 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 498 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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

916 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