Date Calculation to Exclude/Handle Weekends?

  • Thread starter cw via AccessMonster.com
  • Start date
C

cw via AccessMonster.com

I use the following WHERE clause in an Auto-Generated Report (it is always
based on Yesterday's Data):

WHERE (((qrySalesReport_AllCatg.Date)=Date()-1))

This works for Tue, Wed, Thu, Fri, but fails on Mon because of the weekend.
On Monday, I have to manually change the code to:

WHERE (((qrySalesReport_AllCatg.Date)=Date()-3))

Question: How do I change the SQL code to make the WHERE clause ask what day
is it, and if it is Monday, then use -3, otherwise use -1?

Thanks,
cw
 
R

ruralguy via AccessMonster.com

Why not just incorporate a function to do it for you automatically?
Put this code in a standard module named basDateStuff.
Public Function MinusWorkdays(dteStart As Date, intNumDays As Long) As Date

MinusWorkdays = dteStart
Do While intNumDays > 0
MinusWorkdays = DateAdd("d", -1, MinusWorkdays)
If Weekday(MinusWorkdays, vbMonday) <= 5 Then
'-- If you have a Holiday Table use the next IF instead!
' If Weekday(MinusWorkdays, vbMonday) <= 5 And _
IsNull(DLookup("[Holiday]", "tblHolidays", _
"[HolDate] = " & Format(MinusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l")))
Then
intNumDays = intNumDays - 1
End If
Loop
End Function

Then change your query to:
WHERE ((qrySalesReport_AllCatg.Date)=MinusWorkdays(Date,1))
 
C

cw via AccessMonster.com

This looks like some great code and you even anticipated my next question
concerning holidays.
I'll try it out immediatley. One question..

On the tblHolidays, what should the fields be?
ID - AutoNumber
Holiday - Description
HolDate - Date type

Thanks,
cw

Why not just incorporate a function to do it for you automatically?
Put this code in a standard module named basDateStuff.
Public Function MinusWorkdays(dteStart As Date, intNumDays As Long) As Date

MinusWorkdays = dteStart
Do While intNumDays > 0
MinusWorkdays = DateAdd("d", -1, MinusWorkdays)
If Weekday(MinusWorkdays, vbMonday) <= 5 Then
'-- If you have a Holiday Table use the next IF instead!
' If Weekday(MinusWorkdays, vbMonday) <= 5 And _
IsNull(DLookup("[Holiday]", "tblHolidays", _
"[HolDate] = " & Format(MinusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l")))
Then
intNumDays = intNumDays - 1
End If
Loop
End Function

Then change your query to:
WHERE ((qrySalesReport_AllCatg.Date)=MinusWorkdays(Date,1))
I use the following WHERE clause in an Auto-Generated Report (it is always
based on Yesterday's Data):
Click to expand...
[quoted text clipped - 11 lines]
Thanks,
cw
Click to expand...
Click to expand...
 
K

Klatuu

Here is a function that will handle this. It also will handle holidays if
you have a holiday table.

Put this function in a standard module
Change your call from
WHERE qrySalesReport_AllCatg.Date=AddWorkDays(Date(),-1)

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Long) As Date
Dim lngAdd As Long
Dim lngDayCount As Long
Dim lngHolidayCount As Long
Dim dtmNextDate As Date
Dim dtmLowDate As Date
Dim dtmHighDate As Date

If DaysToAdd < 0 Then
lngAdd = -1
Else
lngAdd = 1
End If

AddWorkDays = OriginalDate

Do Until lngDayCount = DaysToAdd
AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
If Weekday(AddWorkDays, vbMonday) < 6 Then
If IsNull(DLookup("[holidate]", "dbo_holiday_list", "[holidate]
= #" & _
AddWorkDays & "#")) Then
lngDayCount = lngDayCount + lngAdd
End If
End If
Loop

End Function
 
R

ruralguy via AccessMonster.com

You have the correct fields and types. Have fun.
This looks like some great code and you even anticipated my next question
concerning holidays.
I'll try it out immediatley. One question..

On the tblHolidays, what should the fields be?
ID - AutoNumber
Holiday - Description
HolDate - Date type

Thanks,
cw
Why not just incorporate a function to do it for you automatically?
Put this code in a standard module named basDateStuff.
Click to expand...
[quoted text clipped - 22 lines]
Click to expand...
 
C

cw via AccessMonster.com

Klatuu, How would I reference the DIM variables in your code and print their
Values on my Report?
- I created a Text Box on my Report in the header section
- What would its Record Source be?

What I'm trying to do now is print the Day that gets selected by your code.

Thanks,
ps: Nice code.
cw

Here is a function that will handle this. It also will handle holidays if
you have a holiday table.

Put this function in a standard module
Change your call from
WHERE qrySalesReport_AllCatg.Date=AddWorkDays(Date(),-1)

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Long) As Date
Dim lngAdd As Long
Dim lngDayCount As Long
Dim lngHolidayCount As Long
Dim dtmNextDate As Date
Dim dtmLowDate As Date
Dim dtmHighDate As Date

If DaysToAdd < 0 Then
lngAdd = -1
Else
lngAdd = 1
End If

AddWorkDays = OriginalDate

Do Until lngDayCount = DaysToAdd
AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
If Weekday(AddWorkDays, vbMonday) < 6 Then
If IsNull(DLookup("[holidate]", "dbo_holiday_list", "[holidate]
= #" & _
AddWorkDays & "#")) Then
lngDayCount = lngDayCount + lngAdd
End If
End If
Loop

End Function
I use the following WHERE clause in an Auto-Generated Report (it is always
based on Yesterday's Data):
Click to expand...
[quoted text clipped - 11 lines]
Thanks,
cw
Click to expand...
Click to expand...
 
Top