Calculating Work Days Between Two Dates

[Shawn]

I am creating an Access database to track actions in an office. I have four
fields that have a pop-up calendar appear, allowing a date in, two
re-assignment dates, and a date out to be assigned (the language for the
calendar and populating those fields is the first six sets of code below, and
works fine). The seventh piece of code is supposed to calculate the number
of elapsed work days between the DateIn and DateOut when you click in that
field. This code calculates correctly; however, when I populate the
DaysElapsed field it simultaneously changes the DateIn field date to one day
after the DateOut field.

1. Can anyone please tell me what I need to do so the DateIn field does not
change when the calculation occurs?

2. Is there a better way (than clicking the DaysElapsed field) to populate
the DaysElapsed field with the calculated integer when I enter the date in
the DateOut field? (The perfect solution would be if the calculation would
run when I populate the DateOut field with a date and the DaysElapsed field
would be populated with the calculated integer.)

Thanks for any assistance. The visual basic language from the database
follows:



Option Compare Database
Dim cboOriginator As ComboBox


Private Sub Calendar4_Click()
cboOriginator.Value = Calendar4.Value
cboOriginator.SetFocus
Calendar4.Visible = False
Set cboOriginator = Nothing
End Sub


Private Sub Ctl1stReassignmentDate_MouseDown(Button As Integer, Shift As
Integer, X As Single, Y As Single)
Set cboOriginator = Ctl1stReassignmentDate
Calendar4.Visible = True
Calendar4.SetFocus
If Not IsNull(cboOriginator) Then
Calendar4.Value = cboOriginator.Value
Else
Calendar4.Value = Date
End If
End Sub


Private Sub Ctl2ndReassignmentDate_MouseDown(Button As Integer, Shift As
Integer, X As Single, Y As Single)
Set cboOriginator = Ctl2ndReassignmentDate
Calendar4.Visible = True
Calendar4.SetFocus
If Not IsNull(cboOriginator) Then
Calendar4.Value = cboOriginator.Value
Else
Calendar4.Value = Date
End If
End Sub


Private Sub DateIn_MouseDown(Button As Integer, Shift As Integer, X As
Single, Y As Single)
Set cboOriginator = DateIn
Calendar4.Visible = True
Calendar4.SetFocus
If Not IsNull(cboOriginator) Then
Calendar4.Value = cboOriginator.Value
Else
Calendar4.Value = Date
End If
End Sub


Private Sub DateOut_MouseDown(Button As Integer, Shift As Integer, X As
Single, Y As Single)
Set cboOriginator = DateOut
Calendar4.Visible = True
Calendar4.SetFocus
If Not IsNull(cboOriginator) Then
Calendar4.Value = cboOriginator.Value
Else
Calendar4.Value = Date
End If
End Sub


Private Sub DaysElapsed_Click()
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [Date] FROM tblHolidays", dbOpenSnapshot)
DateIn = DateIn + 1

intCount = 0

Do While DateIn <= DateOut

rst.FindFirst "[Date] = #" & DateIn & "#"
If Weekday(DateIn) <> vbSunday And Weekday(DateIn) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

DateIn = DateIn + 1

Loop

DaysElapsed = intCount

End Sub

 

[Tim Ferguson]

1. Can anyone please tell me what I need to do so the DateIn field
does not change when the calculation occurs?

Ummm... remove the line that says

DateIn = DateIn + 1

perhaps?

2. Is there a better way (than clicking the DaysElapsed field) to
populate the DaysElapsed field with the calculated integer when I
enter the date in the DateOut field? (The perfect solution would be
if the calculation would run when I populate the DateOut field with a
date and the DaysElapsed field would be populated with the calculated
integer.)

What about the DateOut_AfterUpdate event?


HTH


Tim F

 

[Shawn]

Appreciate the idea on removing the DateIn = DateIn + 1 line, but it does not
fix the problem.

If I remove the line "DateIn = DateIn + 1" before the line "intCount = 0"
the program still changes the DateIn date and counts the day in as day one in
the generated integer. If I remove the line "DateIn = DateIn + 1" between
the lines "End If" and "Loop" then the program counts nothing and I get a
runtime error 6 and debug highlights the line right before "End If".

If I change the line "Do While DateIn <= DateOut" to "Do While DateIn <
DateOut" then the count does not count the day out as part of the generated
integer. Also, the DateIn changes to the same date as the DateOut whereas
before that change it makes the DateIn one day after the DateOut.

Any other ideas?

 

[Tim Ferguson]

Any other ideas?

Not really. I'm afraid that I don't have the time to hand-debug some eighty
lines of code without a single comment between them; no idea of what
controls you have on your forms; nor what your db schema looks like.

There are lots of potential problems, like having field names like "Date",
and not formatting variables before passing them to the db engine, that
probably don't relate to your present problem; but in short it's not really
possible to know what is going wrong here.

I am sorry to sound negative but... the way to get help is (1) to reduce
your problem to the smallest possible issue - something like five or six
lines of code that are not doing what you expect; (2) what steps you have
taken to investigate or correct the problem yourself; (3) enough background
for someone else to replicate the problem.

It also occurs to me that there has to be a better way of calculating a
count of workdays anyway... something along the lines of working out the
number of weeks between the dates, and returning 5 * weeksCount; adjusting
for where the start and end weekdays are; and subtracting any holidays
between them. This should be no more than a bit of integer maths, and a
DCount() function call. The whole prospect of iterating every single day
between two random dates fills me with shudders -- still, you get your code
where you can.

Best wishes


Tim F

 

[Shawn]

Appreciate the advice on how get help with this type of problem. I am new to
this and thought I was doing the right thing by providing the entire code. I
have done several searches on the web looking for ways to insert a pop-up
calendar and the code that does that came from what I found. The same is
true for the code calculating the workdays. I really did not think it should
be that hard either, but the code I listed is the easiest solution I could
find. I found it at http://www.mvps.org/access/datetime/date0006.htm which
is a web site called The access Web. I went with "WorkingDays2" and that
piece of code includes comments. It does calculate the elapsed work days
just fine, and if I could just get it to not reset the starting date it would
be adequate for my needs.

I am continuing to play with the code and have picked up some books on
visual basic, but am really doing this starting from a knowledge base of
zero, so thought maybe I was missing something very obvious to the pros.
I'll keep trying. Thanks for your input.

 

[Tim Ferguson]

I went with "WorkingDays2" and that
piece of code includes comments. It does calculate the elapsed work
days just fine, and if I could just get it to not reset the starting
date it would be adequate for my needs.

Except you didn't... go with it, that is. You have introduced errors into
the original code.

Principally, in the original code the date increment uses a variable
called StartDate, which is a parameter and, because it's passed ByVal it
won't mess up anything outside the procedure.

In your version, you use something called DateIn, which is not declared
anywhere that I can see, but your post suggests that it's the name of a
control which is bound to a database field. If you change that, then you
are going to create side-effects. I appreciate that you are still
learning VBA and this is an important lesson about encapsulation -- don't
let any procedure do anything to anything outside unless it's (a)
documented, (b) deliberate, (c) documented, and (d) desirable. And make
sure you document it too.

I have not looked at this particular code before although Dev's site is
usually of a very high standard. To my taste, this example is low on
commenting, uses a brute-force approach, is a bit wasteful of resources
and not internationally-safe. Still, I don't have a better example to put
up against it, so I'd better not go any further!

I would advise you to look at the original code very hard, because it
probably does give you enough information to make your own work. The
simplest fix is to Dim a local date variable to hold the looping date,
and set it from the DateIn control at the start of the procedure.



Best wishes


Tim F