previous working day

[mrbudgie]

how do i put criteria into a query to only retrieve records from the previous working day, but if the previous day is saturday or sunday (current day monday) it knows to go back to friday and ignore weekends as they are not working days.

thanks in advance guys

ian

 

[danielc]

hi,

you can try with Weekday
Weekday([date])

the rest can be solved with iif .

dny

 

[mrbudgie]

i have had a go but no luck what would you suggest to use with the weekday([date]) to do it for me

thanks ian

 

[raskew]

Hi -

Try placing this as the criteria for your date field:

Date()-IIf(Weekday(Date())<3,1+Weekday(Date()),1)

HTH - Bob

 

[raskew]

Hey -

Since you are asking forum members to provide their 'expertise' to resolve your problem, could you at least have the courtesy to say 'it worked', 'it' didn't work', or whatever.

Bob

 

[student]

Worked great for me, thanks raskew. Can you confirm whether or not this will work for Bank Holidays as well? Sorry - couldn't figure it out from this :$

 

[student]

Worked great for me, thanks raskew. Can you confirm whether or not this will work for Bank Holidays as well? Sorry - couldn't figure it out from this :$

 

[JimSnyder]

I know it is an old thread, but these get hit all the time with searches. I have two functions that are easy to read and easy to test that will return either day or date and can be called within a query:

Public Function PriorWkDay() As String
Dim offsetDay As Integer

If Weekday(Date) = 1 Then ' Monday
offsetDay = 3
ElseIf Weekday(Date) = 7 Then ' Sunday
offsetDay = 2
Else
offsetDay = 1 ' Any other day
End If
PriorWkDay = Format(Date - offsetDay, "YYYY-MM-DD")
' MsgBox "Prior work day is: " & PriorWkDay
End Function

Public Function PriorWkDate() As Date
Dim offsetDay As Integer

If Weekday(Date) = 1 Then ' Monday
offsetDay = 3
ElseIf Weekday(Date) = 7 Then ' Sunday
offsetDay = 2
Else
offsetDay = 1 ' Any other day
End If
PriorWkDate = CDate(Date - offsetDay)
' MsgBox "Prior work day is: " & PriorWkDate
End Function

EDIT: Lost my formatting when placed as preformatted text. Open to suggestions...

 

[Isskint]

Couple of good functions Jim.

Maybe think about providing a way to indicate what the week/weekend days are? In this day and age many people do not work Mon - Fri.

 

[JimSnyder]

I may not be able to reply all the time. Our internet security team has blocked my last two replies.

 

[JimSnyder]

These functions run daily to be part of automating a process. They they are used to automate pulling files off an sFTP site and placing them locally. My customer makes reports manually and only supplies files M-F. They can be altered by changing the CASE offset to have start day of the week to be Sunday instead of Monday, or start and end a week wherever it is needed.

To use these, I add a module to Access and add the functions to the module. They can then be used in SQL as a function call returning the date you need in either string or date format. They do not account for Holidays, nor do they rely on anything other than the day of the week.

If you work a four day week, add an additional CASE statement for the third day. If you work a six day work week, remove the CASE statement with the highest days subtracted. Since they rely on weekday only, time of day is not a concern.

EDIT: Apparently, only 'Post Quick Reply' is not screened by my network security folks