Now we'll walk through a function step-by-step
that will allow us to determine if any given date
(like the dates on either end of our date span)
are weekend days. As there is no predefined
weekend function in Access, we have to create
our own:
Function IsWeekend(dtm As Date) As
Boolean
Dim rv As Boolean
Select Case Weekday(dtm)
Case Is =
vbSaturday, vbSunday
rv = True
Case Else
rv = False
End Select
IsWeekend = rv
End Function
The input to this function IsWeekend is a date;
the output is True or False; true if the date is
Saturday or Sunday, false otherwise.
Let's go though the code line by line.
Dim rv As Boolean
This line creates a variable rv (which stands
for Return Variable) to make our lives easier. I
could omit this variable and replace every "rv"
in the code with "IsWeekend", however I much
prefer to type two letters rather than seven
when I'm writing code.
Our next section of code is bracketed at the
beginning with Select Case and at the end with
End Select. The Select Case construct is
similar to a set of IF/THEN statements except
that the code between each line that starts
with the word Case is evaluated in a stepwise
manner. If the statement is true, it will execute
all the code between that Case line and the
next Case line. If it's not true, it will skip to the
next Case line. Once one of the lines starting
with Case is satisfied (rv is deemed to be
either true or false), the function jumps to End
Select.
The line
Select Case Weekday(dtm)
uses a built-in Access function called Weekday
to evaluate our argument dtm (date). This
function takes one variable, a date, and
returns a number from 1 to 7 representing the
day of the week. There's a bit of complexity
behind the scenes in determining the first day
of the week. We avoid that by using the
Access internal constants vbSaturday and
vbSunday .
From there the code is straightforward and
mainly a matter of syntax:
Case is = vbSaturday, vbSunday
translates to "if the case of the incoming date
is a Saturday or a Sunday then go to the next
line, otherwise jump to the next Case
Statement."
If it is a Saturday or a Sunday the line
rv = true
is executed, which means that our return
variable is now set to true. If the incoming date
is not a Saturday or a Sunday the next line to
be executed is
Case Else
The case else line within a Select Case
construct basically says that if none of the
case statements evaluates to true then do the
Select Case statements. Theoretically we
could include lines like
Case is = vbMonday
Rv = false
Case is = vbTuesday
Rv = false
However if it's not a Saturday or a Sunday,
then we know the date is not a weekend so we
can just jump to our Case else statement. The
line following Case Else
rv = False
simply sets our return variable to false.
The next line
IsWeekend = rv
sets our function return value equal to our rv
variable, and then we complete the function
with our
End Function
