Date Restrictor for last business day

[Chuck]

Hi,

I want to run a query and build a report for an
application that will get sales from the last business
day. Our company is not open on Saturday or Sunday. I
can't use date()-1 because if I were to run this on
Sunday it would not register any sales. How do I run a
query with a date restrictor that captures the last
business day?

Thanks,

Chuck

 

[fredg]

Hi,

I want to run a query and build a report for an
application that will get sales from the last business
day. Our company is not open on Saturday or Sunday. I
can't use date()-1 because if I were to run this on
Sunday it would not register any sales. How do I run a
query with a date restrictor that captures the last
business day?

Thanks,

Chuck

You only wish data for the one day, Friday, not for the full week
ending on Friday, right?
And this is only going to be run on a Saturday or a Sunday, correct?

Saturday is weekday 7, so either subtract one day if it's Saturday, or
subtract 2 days because if it ain't Saturday it's gotta be Sunday.

WHERE YourTable.DateField =IIf(Weekday(Date())=7,Date()-1,Date()-2);

If your first day of the week starts on a different day than Sunday,
see Access help for the additional arguments of the Weekday function,
and adjust the code accordingly.

 

[Dale Fye]

Chuck,

I think Fred missed the point that you can run this report any day, but
whenever you run it you want it to reference the previous workday, so if you
ran the report on Sat, Sun, or Monday, it would show Fridays stuff, and on
the other days it would show the previous day. How are you handling
holidays?

At anyrate, I think the easiest way would be to write a function, something
like the following. This starts off by determining the number of days to
subtract from the date passed - if the date passed is a sunday (1), then
subtract 2, if Monday(2) then subtract 3, otherwise, subtract 1. Then, if
you are concerned with holidays, create tbl_Holidays and enter the holidays
that you won't be working. The last set of code checks to see whether the
PrevWorkday that has already been calculated is a holiday, and if it is, it
calls itself again.

Public Function PrevWorkday(dtToday As Date) As Date

Dim intWeekday As Integer
Dim intOffset As Integer

intWeekday = DatePart("w", dtToday)
Select Case intWeekday
Case 1
intOffset = -2
Case 2
intOffset = -3
Case Else
intOffset = -1
End Select

PrevWorkday = DateAdd("d", dtToday, intOffset)

'Insert code here to test to determine whether
'the PrevWorkday is a holiday. If so, call
'PrevWorkday with that date
While Not IsNull(DLookup("HolidayDate", "tbl_Holidays", "HolidayDate =
#" & PrevWorkday & "#"))
PrevWorkday = PrevWorkday(PrevWorkday)
Wend

End Function

HTH
Dale

 

[ChuckW]

Dale,

Thanks for your help. I am a bit of a novice user and
don't fully understand how to proceed. Do I go into the
Visual Basic editor and create the function there? I
checked the help on how to do this but I did not list
anything.

Thanks,

Chuck

-----Original Message-----
Chuck,

I think Fred missed the point that you can run this report any day, but
whenever you run it you want it to reference the previous workday, so if you
ran the report on Sat, Sun, or Monday, it would show Fridays stuff, and on
the other days it would show the previous day. How are you handling
holidays?

At anyrate, I think the easiest way would be to write a function, something
like the following. This starts off by determining the number of days to
subtract from the date passed - if the date passed is a sunday (1), then
subtract 2, if Monday(2) then subtract 3, otherwise, subtract 1. Then, if
you are concerned with holidays, create tbl_Holidays and enter the holidays
that you won't be working. The last set of code checks to see whether the
PrevWorkday that has already been calculated is a holiday, and if it is, it
calls itself again.

Public Function PrevWorkday(dtToday As Date) As Date

Dim intWeekday As Integer
Dim intOffset As Integer

intWeekday = DatePart("w", dtToday)
Select Case intWeekday
Case 1
intOffset = -2
Case 2
intOffset = -3
Case Else
intOffset = -1
End Select

PrevWorkday = DateAdd("d", dtToday, intOffset)

'Insert code here to test to determine whether
'the PrevWorkday is a holiday. If so, call
'PrevWorkday with that date
While Not IsNull(DLookup

("HolidayDate", "tbl_Holidays", "HolidayDate =