How to write a query that add 1 working day to the date

[Boon]

Hi,

I have a column Date_Data. The value in this field is in a date time format.
(e.g. 4/5/2009)

How can I write a query that will move the date to the next working day
(M-F). If the current date is Monday, I want Tuesday. If the current date
is Friday, I want Monday. If the current date is Saturday or Sunday, I want
Monday.


Thanks you,
B

 

[raskew via AccessMonster.com]

Hi -

Try copying/pasting this to a standard module then testing by calling from
the debug window as shown:

Public Function UpBusDays3(pstart As Date, _
pNum As Integer, _
Optional pAdd As Boolean = True) As Date
'*******************************************
'Purpose: Add or subtract business days
' from a date
'Coded by: raskew
'Inputs: 1) +) ? UpBusDays3(#2/17/06#, 3, True)
' 2) -) ? UpBusDays3(#2/22/06#, 3, False)
'Output: 1) +) 2/22/06
' 2) -) 2/17/06
'*******************************************

Dim dteHold As Date
Dim i As Integer
Dim n As Integer

dteHold = pstart
n = pNum
For i = 1 To n
If pAdd Then 'add days
dteHold = dteHold + IIf(WeekDay(dteHold) > 5, 9 - WeekDay(dteHold),
1)
Else 'subtract days
dteHold = dteHold - IIf(WeekDay(dteHold) < 3, Choose(WeekDay
(dteHold), 2, 3), 1)
End If
Next i
UpBusDays3 = dteHold

End Function

HTH - Bob

 

[KARL DEWEY]

Try this --
IIF(WeekDay([Date_Data]+1) Between 2 AND 6, [Date_Data]+1,
IIF(WeekDay([Date_Data]+2) Between 2 AND 6, [Date_Data]+2, [Date_Data]+3))

 

[raskew via AccessMonster.com]

And how to handle that if you want to add 37 business days this time and 16
business days next time?

Bob

Try this --
IIF(WeekDay([Date_Data]+1) Between 2 AND 6, [Date_Data]+1,
IIF(WeekDay([Date_Data]+2) Between 2 AND 6, [Date_Data]+2, [Date_Data]+3))

Hi,

[quoted text clipped - 8 lines]

Thanks you,
B

 

[KARL DEWEY]

Table CountNumber has integer field CountNUM with number from 0 (zero) to
your maximum spread.
Try these two queries (I do know subqueries) ---
Dates_Bus_Days_Forward_1 --
SELECT DateAdd("d",[CountNUM],Date()) AS Dates, CountNumber.CountNUM, [Enter
number of business days from today] AS NumBusDays
FROM CountNumber
WHERE (((CountNumber.CountNUM)<>0 And (CountNumber.CountNUM)<=[Enter number
of business days from today]+(([Enter number of business days from
today]\5)*2)+4) AND ((Weekday(DateAdd("d",[CountNUM],Date()))) Between 2 And
6))
ORDER BY DateAdd("d",[CountNUM],Date());

SELECT TOP 1 Dates_Bus_Days_Forward_1.Dates
FROM Dates_Bus_Days_Forward_1, Dates_Bus_Days_Forward_1 AS
Dates_Bus_Days_Forward_1_1
WHERE
(((DateDiff("d",[Dates_Bus_Days_Forward_1_1].[Dates],[Dates_Bus_Days_Forward_1].[Dates]))=[Dates_Bus_Days_Forward_1].[NumBusDays]-1))
ORDER BY Dates_Bus_Days_Forward_1.Dates;

And how to handle that if you want to add 37 business days this time and 16
business days next time?

Bob

Try this --
IIF(WeekDay([Date_Data]+1) Between 2 AND 6, [Date_Data]+1,
IIF(WeekDay([Date_Data]+2) Between 2 AND 6, [Date_Data]+2, [Date_Data]+3))

Hi,

[quoted text clipped - 8 lines]

Thanks you,
B

 

[raskew via AccessMonster.com]

Emmm?!

Slick!

Bob

Table CountNumber has integer field CountNUM with number from 0 (zero) to
your maximum spread.
Try these two queries (I do know subqueries) ---
Dates_Bus_Days_Forward_1 --
SELECT DateAdd("d",[CountNUM],Date()) AS Dates, CountNumber.CountNUM, [Enter
number of business days from today] AS NumBusDays
FROM CountNumber
WHERE (((CountNumber.CountNUM)<>0 And (CountNumber.CountNUM)<=[Enter number
of business days from today]+(([Enter number of business days from
today]\5)*2)+4) AND ((Weekday(DateAdd("d",[CountNUM],Date()))) Between 2 And
6))
ORDER BY DateAdd("d",[CountNUM],Date());

SELECT TOP 1 Dates_Bus_Days_Forward_1.Dates
FROM Dates_Bus_Days_Forward_1, Dates_Bus_Days_Forward_1 AS
Dates_Bus_Days_Forward_1_1
WHERE
(((DateDiff("d",[Dates_Bus_Days_Forward_1_1].[Dates],[Dates_Bus_Days_Forward_1].[Dates]))=[Dates_Bus_Days_Forward_1].[NumBusDays]-1))
ORDER BY Dates_Bus_Days_Forward_1.Dates;

And how to handle that if you want to add 37 business days this time and 16
business days next time?

[quoted text clipped - 10 lines]