G
Guest
I am using a form with a startdate, and have a sql server function to
calculate the end date(30 days not including weekends and holidays), but am
have difficulty calling the function.
My Startdate form field is txtStartdate
My end date field is txtEnddate
When I click the Save button, I need the endDate inserted into the
txtenddate field.
How do I construct the vba code to get the results?
Here is my SQL server function:
'************************************************* ************
USE [oprmi]
GO
/****** Object: UserDefinedFunction [dbo].[fn_AddBusinessDays] Script Date:
09/06/2009 21:17:48 ******/
SET ANSI_NULLSON
GO
SET QUOTED_IDENTIFIERON
GO
ALTER function [dbo].[fn_AddBusinessDays]
--Accepts Date X and Integer Y
(
@dtBeginDate datetime,
@iBizDays int
)
RETURNSdatetime
--Result is Date Z that is Y business days after supplied Date X
AS
BEGIN
DECLARE @dtEndDate datetime
DECLARE @dtDateHolder datetime
DECLARE @dtNextHol datetime
DECLARE @dtNextPH datetime
SET @dtDateHolder = @dtBeginDate
SET @dtEndDate =DATEADD(d,@iBizDays,@dtBeginDate)
--Find the first Public Holiday Date
select @dtNextHol =MIN(holiday)FROM Holidays where holiday >=@dtDateHolder
WHILE(@dtDateHolder <= @dtEndDate)
BEGIN
--Is the date being checked a Saturday or Sunday?
IF((DATEPART(dw, @dtDateHolder)in(1, 7))
--Is the date being checked a holiday?
OR(DATEADD(dd, 0,DATEDIFF(dd, 0, @dtDateHolder))=@dtNextPH))
--NOTE: DATEDIFF trick used above to discard TIMESTAMP
BEGIN
-- Extend the date range for Weekends and Holidays by one day
SET @dtEndDate =DATEADD(d,1,@dtEndDate)
if(DATEADD(dd, 0,DATEDIFF(dd, 0, @dtDateHolder))=@dtNextHol)
BEGIN
-- get the next public holiday date
select @dtNextHol =MIN(holiday )FROM Holidays where holiday >@dtDateHolder
END
END
--Move to the next day in the range and loop back to check it
SET @dtDateHolder =DATEADD(d,1,@dtDateHolder)
END
--Respond with newly determined end date
RETURN @dtEndDate
END
'*************************************************************************************************************
My database is SQL Server 2005 and I have a holiday table as part of the
solution.
I have tried this in vba code on the form's SAVE button:
Dim strEnd30 as Date
Format(strEnd30, "mm/dd/yyyy") = "SELECT [dbo].[fn_AddBusinessDays]
('09/01/2009','30')"
txtEnddate = strEnd30
I am using Access 2007 and SQL Server 2005.
thanks
all
calculate the end date(30 days not including weekends and holidays), but am
have difficulty calling the function.
My Startdate form field is txtStartdate
My end date field is txtEnddate
When I click the Save button, I need the endDate inserted into the
txtenddate field.
How do I construct the vba code to get the results?
Here is my SQL server function:
'************************************************* ************
USE [oprmi]
GO
/****** Object: UserDefinedFunction [dbo].[fn_AddBusinessDays] Script Date:
09/06/2009 21:17:48 ******/
SET ANSI_NULLSON
GO
SET QUOTED_IDENTIFIERON
GO
ALTER function [dbo].[fn_AddBusinessDays]
--Accepts Date X and Integer Y
(
@dtBeginDate datetime,
@iBizDays int
)
RETURNSdatetime
--Result is Date Z that is Y business days after supplied Date X
AS
BEGIN
DECLARE @dtEndDate datetime
DECLARE @dtDateHolder datetime
DECLARE @dtNextHol datetime
DECLARE @dtNextPH datetime
SET @dtDateHolder = @dtBeginDate
SET @dtEndDate =DATEADD(d,@iBizDays,@dtBeginDate)
--Find the first Public Holiday Date
select @dtNextHol =MIN(holiday)FROM Holidays where holiday >=@dtDateHolder
WHILE(@dtDateHolder <= @dtEndDate)
BEGIN
--Is the date being checked a Saturday or Sunday?
IF((DATEPART(dw, @dtDateHolder)in(1, 7))
--Is the date being checked a holiday?
OR(DATEADD(dd, 0,DATEDIFF(dd, 0, @dtDateHolder))=@dtNextPH))
--NOTE: DATEDIFF trick used above to discard TIMESTAMP
BEGIN
-- Extend the date range for Weekends and Holidays by one day
SET @dtEndDate =DATEADD(d,1,@dtEndDate)
if(DATEADD(dd, 0,DATEDIFF(dd, 0, @dtDateHolder))=@dtNextHol)
BEGIN
-- get the next public holiday date
select @dtNextHol =MIN(holiday )FROM Holidays where holiday >@dtDateHolder
END
END
--Move to the next day in the range and loop back to check it
SET @dtDateHolder =DATEADD(d,1,@dtDateHolder)
END
--Respond with newly determined end date
RETURN @dtEndDate
END
'*************************************************************************************************************
My database is SQL Server 2005 and I have a holiday table as part of the
solution.
I have tried this in vba code on the form's SAVE button:
Dim strEnd30 as Date
Format(strEnd30, "mm/dd/yyyy") = "SELECT [dbo].[fn_AddBusinessDays]
('09/01/2009','30')"
txtEnddate = strEnd30
I am using Access 2007 and SQL Server 2005.
thanks
all