After Hours

Ask something
Question Locked

Missing out weekends in Microsoft Access

0 helpful
5 replies

by tony-oborne

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I'm putting together a database to run a gas servicing programme. I need to run a query that looks at the previous service date and then generates a next service date that is ten months after the previous service date. The only complication is that I need it to avoid weekends (and holidays as well if possible!)

Thanks in advance!

5 replies

by darryl~

Does it have to be 10 month? Can it be 41 or 42 weeks instead? If you were to take 42 multiplied by 7, you would have the number of days to the same day of the week 42 weeks for now...it wouldn't catch holidays but it would avoid weekends assuming that the starting date was not during the weekend.....so if I had my car in on a Monday for service & you booked my next one 42 weeks form now, it would hit a Monday.

Watch out for Leap Year

Just a thought. :)

edited for leap year

by maevinn

You'll need to build a table called Holidays and enter in a couple years worth of holidays (since not all holidays occur on the same day each year).

SELECT Table2.myDate
FROM Table2
WHERE (((Table2.myDate)=GetWeekDays([myDate])));

Function GetWeekDays(myDate)

If Weekday(myDate) = 1 Or Weekday(myDate) = 7 Then
GetWeekDays = Null
ElseIf myDate in (Select Date From Holidays) Then
GetWeekDays = Null
Else
GetWeekDays = myDate
End If

End Function

by tony hopkinson

A days table, which can have a lot of other value, is the ways to go with this one.
Most of the other solutions are iffy, require complex functions or while loops.
Missing weekends is easy

Declare @NextServiceDate DateTime
Select @NextServiceDate = DateAdd(month, 10,[LastServiceDate])
if DOW(@NextServiceDate = 7
Select @NextServiceDate = DateAdd(day,2,@NextServiceDate)
if DOW(@NextServiceDate = 1
Select @NextServiceDate = DateAdd(day,1,@NextServiceDate)

or some such
Check out the 10 month requirement, 300 days or n working days is much easier to deal with calculation wise. 10 months sounds more like an approximation in a manual system.

by tony-oborne

I decided to go with using 294 days instead of 10 months and having a table of holidays. Worked a treat, thanks again!

This discussion is closed to new replies.