Dates

[Guest]

I have a db which records employee attendance.
I would like three buttons on my front page which will show:
last working day/
current/
next working days absences.

I understand that i can use date()-1 and date()+1...however if the current
date is friday then the next day one comes up with saturdays abences (i.e.not
a working day) and i would like it to comes up with mondays absences.

Does anyone have any suggestions? Any help much appreciated!

Chloe

 

[Douglas J. Steele]

The Weekday function returns 1 for Sunday, 2 for Monday and so on until 7
for Saturday.

Since you want to add 3 days to Friday and 2 days to Saturday (and 1 day to
every other day), that means that the next workday can be calculated as

DateAdd("d", IIf(Weekday(Date) > 5, 9 - Weekday(Date), 1), Date)

The previous workday would be

DateAdd("d", IIf(Weekday(Date) < 3, -1 - Weekday(Date), -1), Date)

 

[Guest]

thanks for explaining that, where do i enter this information in my query?!

 

[Simon Pollock]

Use a combination of the WeekDay and DateAdd functions.e.g.

Public Function NextWorkDay(d As Date) As Date
Dim i As Byte
Select Case Weekday(d)
Case 6 'Friday
i = 3
Case 7 'Saturday
i = 2
Case Else
i = 1
End Select
NextWorkDay = DateAdd("d", i, d)
End Function

Simon

 

[Douglas J. Steele]

You'd enter those formulae instead of Date()-1 and Date()+1 wherever you'd
intended to use them.