WorkingDays Function

[Guest]

I found a VBA function on the internet that would help me calculate the
number of working days if I enter a BeginDate and an EndDate. It is supposed
to determine if there are any Saturdays and Sundays inbetween and including
the dates I enter for BeginDate and EndDate. I am a senior and I need help.
In my form, where do I put this function--in design view--form properties? or
WorkingDays control properties. I can't seem to get it to work. The
function I found is called a Public Function--how do I get it to work?

 

[Guest]

1st off, it is hard for anyone to help you when we do not have the code to
review. it would be best to post it when you are asking questions about it.


Typically, you'd post the code either in a separate module or the form
module. Then simply call it as needed. The Function must require 2 input
variables (BeginDate,EndDate). It all depends on how you want it all to
work. Perhaps you want to get the value when you click a button or perhaps
automatically whenever either date is updated...??? if it is the later, then
for both the BeginDate and EndDate controls you'll need to create an
afterupdate event where you use the function and return the value to another
control (or msgbox). Something like

Me.Numday=FunctionName(BeginDate,EndDate)

We really need more detail to help you though. what are the names of the
controls on your form? Where should the result be returned (to a form
control -- what name or to the user using a message boc?). When should the
value be calculated (automatically or when the user presses a button)?

 

[Guest]

It should go in a standard module. That way it can be called from anywhere
in your application. As an example, I have a standard module named
modDateFunctions that contains many date manipulation functions. I always
include it in every application, becuase I know I will need it.

 

[Guest]

Thanks for replying, Daniel.

The controls in my database form are: Name (text), StartDate (Date), EndDate
(Date), and WorkingDays (Number).

I want the WorkingDays to calculate automatically and show on the form—right
after the StartDate and EndDate are entered.

The code I found on the Internet is:

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer

On Error GoTo Err_WorkingDays

Dim intCount As Integer

‘StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case WeekDay(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function

How do I get it to work? When I go into the form design and form
properties, two line of a sub procedure appear and if I try to put the
function in the middle—it doesn’t work. Please be patient with me—I love
learning—and wish I was forty years younger.

Thank you, Daniel.

 

[Guest]

First, rename the control you have named Name. It is an Access reserved
word. It being a text box, I would suggest the name txtName.
Now, if you want it to calculate after both the beginning and ending dates
have been entered, create a function in your form module that you will call
in the after update events of the StartDate and EndDate controls.

Private Sub BeginDate_AfterUpdate()

Call FillWorkDays

End Sub

(Same for the EndDate control)

Private Sub FillWorkDays()

If Not IsNull(Me.StartDate) and Not IsNull(Me.EndDate) Then
Me.WorkingDays = WorkingDays(Me.StartDate, Me.EndDate)
Else
Me.WorkingDays = Null
End If

End Sub

Again, naming is an issue here. A procedure named the same as a control
will certainly confuse humans and may confuse Access. I would suggest you
read up on some good object naming conventions:

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_20naming.asp

 

[Guest]

Klatuu
What do you mean by a "standard module"?--and where does it go--and what
makes it work?
Fay

 

[Guest]

A standard module is one you find in the database window when you select
Modules. It is VBA code that is not attached to a report or form. They are
used for code that can be called from anywhere in the application.

If you already have a module that has common procedures (Functions and
Subs), open it in design view and paste the code in there. If not, create a
new module to paste the code into.

It works by calling the function and passing the arguments to the function
it needs to perform its work. The function uses the arguments passed to it,
performs the work, and returns a value to the calling code.

 

[Guest]

Me.WorkingDays = WorkingDays(Me.StartDate, Me.EndDate) comes up with a
Mismatch Type Error.

What does Me mean?

Sorry--I'm trying to learn.

Fay

 

[Guest]

Me.WorkingDays = WorkingDays(Me.StartDate, Me.EndDate) comes up with a
Mismatch Type Error.

What does Me mean?
Fay

 

[Guest]

A type mismatch means the data type of some data item is not compatible with
or cannot be coerised another data item. Looking at the code, I don't see
anything obvious; however, sometimes date data types can be problematic. I
don't know where you are and therefore don't know what regional settings you
use for dates, but the value in the form controls you are passing as dates
must be in a format that can be converted from text into a date. In U.S.
format, it would need to be something like 5/2/2007 for today. You may also
want to use the CDate function to see if that helps.

Me.WorkingDays = WorkingDays(CDate(Me.StartDate), CDate(Me.EndDate))

And again, I would strongly recommend you change the name of the control so
it is not the same as the function name. This, as I stated earlier, could be
a problem.