calendar type question

[Guest]

Is there any way in Access to bring in a calendar to help calculate on a
report? That probably doesn't make much sense stated that way, but currently
we have spreadsheets that track employees leave, and the leave is based off
of the number of weekdays in that given month multiplied by 8. So for
February, leave is figured off of how many hours the person worked divided by
160 hrs max. If they work less then that 160 they get the percentage, if
more, they just get 100%. But is there anyway for access to know how many
weekdays are in a month without having to enter this information everytime?

 

[Guest]

You could do it in code with a function such as this:

Public Function WeekdaysInMonth(intYear As Integer, IntMonth As Integer) As
Integer

Dim dtmFirstDay As Date, dtmLastDay As Date
Dim dtmDate As Date
Dim intDayCount As Integer

' get first day off month
dtmFirstDay = DateSerial(intYear, IntMonth, 1)
' get last day of month
dtmLastDay = DateAdd("m", 1, dtmFirstDay) - 1

'count weekdays in month
For dtmDate = dtmFirstDay To dtmLastDay
If Weekday(dtmDate, vbMonday) < 6 Then
intDayCount = intDayCount + 1
End If
Next dtmDate

WeekdaysInMonth = intDayCount

End Function

Just put the function in a standard module and call it, passing the year and
month into it as its arguments, to get the number of workdays. You could for
instance have two unbound combo boxes on a form for selecting the year and
month and a third control with a ControlSource property of:

=WeekdaysInMonth([cboYear], [cboMonth])

The other way is to have a Calendar table, which is simply a table of all
dates over a period of time, 2000 to 2020. Other columns in this table can
be used for indicating weekdays, public holidays, whatever else you might
want (the list is endless!). You can then use the DCount function in code to
get values from the table, or you can join the Calendar table to other tables
with date columns in queries.

 

[Guest]

If you use a calendar table is that something you can just copy from
somewhere or do I actually have to find out all those dates for that span and
enter them?

 

[Guest]

That did work, although I think the process is going to be to complicated, at
least for my blood, to work. We have so many variables that need to be
tracked and can change, that I don't think I have the know how to lay it all
out to work correctly. Thanks for that info though, if I do try and tackle
this, that will help out.

 

[Guest]

You have to create the calendar table yourself, There are various ways to do
this. One is to fill down a column serially in Excel and then import that
into Access as a table. Another is to do it in code. I wrote the following
function to do it. It allows to include only certain days of the week in the
table, so if you wanted a calendar of just weekdays named WeekdayCalendar,
covereing 2000 to 2020 say, you'd call it as follows, which you can do from
the debug window (AKA the immediate pane):

MakeCalendar "WeekdayCalendar", #01/01/2000#, #12/31/2020#, 2,3,4,5,6

If you wanted one called Calendar for all dates:

MakeCalendar "Calendar", #01/01/2000#, #12/31/2020#, 0

In your case you could either use the former or you could create one of all
days then add an IsWeekday Boolean (Yes/No) column and update it with a
simple update query:

UPDATE Calendar
SET IsWeekday = TRUE
WHERE WEEKDAY(CalDate,2) < 6;

How exactly you'd use the table in your case I'd need to know more about
just what you need to do.

Here's the code for the function. Just paste it into any standard module in
your database:

Public Function MakeCalendar(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)

' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)

Dim cmd As ADODB.Command
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tbl = cat(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
cmd.CommandText = strSQL
cmd.Execute
Else
Set cmd = Nothing
Exit Function
End If
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strTable & _
"(calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
cmd.CommandText = strSQL
cmd.Execute

If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
cmd.CommandText = strSQL
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
cmd.CommandText = strSQL
cmd.Execute
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
cmd.CommandText = strSQL
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
cmd.CommandText = strSQL
cmd.Execute
End If
Next varDay
Next dtmDate
End If

Set cmd = Nothing

End Function