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

 

[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))

 

[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