Yes, WorkingDays2 is a function that returns the number of work days
between
two days. The dates are unbound values tied to a form. This is my code
from
an access website:
Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)
'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above
intCount = 0
Do While StartDate <= EndDate
rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If
StartDate = StartDate + 1
Loop
WorkingDays2 = intCount
Exit_WorkingDays2:
Exit Function
Err_WorkingDays2:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select
End Function
'*********** Code End **************
Are you saying I should paste your code into another module? And then
change
the AfterUpdate property of each textbox? Before I do this, I want to be
clear about what I'm trying to achieve. My form right now successfully
calculates the working days between two unbound dates in a form. Now this
"Days" textbox works and I can use to divide the total number of records
between two dates by the number of days to get records per day (and other
operations like this). What I could not get it to do was populate as in
the
example I wrote? Will this accomplish that? Forgive me being dense, I am
adding more tools to my knowledge of SQL and VBA, but sometimes I get
lost. I
don't understand why I can use this calculated textbox and divide it by
other
fields, but I can't populate it as a constant number in the example I
showed.
Thank you very much,
Amin
Private Function CalcWorkDays()
If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If
End Function
Klatuu said:
There are a couple of things to consider here.
First, it appears WorkingDays2 is a function that returns the number of
work
days between the two day. That is good.
Now, it also appears your expression is in the control source of a text
box
on your form and that [StartDate] and [EndDate] are either fields in your
table, controls on your form, or both.
One of the basic rules of database normalization is you do not store
values
in a tabe that can be calculated from available static data. That is, if
StartDate and EndDate are fields in your table, then they should not be
stored at all, but displayed with a caculated control as you are doing
now.
On the other hand, if the dates are unbound values you enter in the form
but
are not stored in the table, then you do want to store the value. But,
since the control source is used to display the calculation, you will
have
to populate the text box another way and bind the control to the field in
your table that stores the number of days.
An easy way to do that would be to write a function that does the
calculation and populates the control and call it from the AfterUpdate
event
of both date controls. It would look like this:
Private Function CalcWorkDays()
If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If
End Function
Note, it will not update until both dates are entered.
Now, to use it, you put the following expression directly in the
AfterUpdate
event text box in the properties dialog for both date controls.
=CalcWorkDays()
That will cause the function to execute whenver the user enters a value
in
either control, but the control to display and store the work days will
not
populate until both have a date.