VBA Code

[Guest]

Hi,

I really need some help with the following. I want to add a text box to a
form that displays the amount of days that it has taken to resolve a query.
For example i have " date raised" and "date Responded" i want the new text
box to show the how many days it has taken from the date raised to date
resolved. Excluding weekends. I have through this forum picked up some code
for this but i dont know how to link the code to the text box. Can anyone
help? Is code really necessary? or is there another way of doing this.
Thanks

 

[ruralguy via AccessMonster.com]

Here's a function you cah put in a standard module named basDateFunctions

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'-- Return the number of WorkingDays between StartDate and EndDate
On Error GoTo err_workingDays

Dim intCount As Integer

If IsDate(StartDate) And IsDate(EndDate) Then
If EndDate >= StartDate Then
'-- Force Incoming Dates into consistant format
StartDate = CDate(Format(StartDate, "Short Date"))
EndDate = CDate(Format(EndDate, "Short Date"))

intCount = 0
Do While StartDate < EndDate
StartDate = StartDate + 1
If Weekday(StartDate, vbMonday) <= 5 Then
'-- Use the following code if you have a "Holiday" table
' If Weekday(StartDate, vbMonday) <= 5 And IsNull(DLookup("[Holiday]",
"tblHolidays", "[HolDate] = #" & StartDate & "#")) Then
intCount = intCount + 1
End If
Loop
WorkingDays = intCount
Else
WorkingDays = -1 '-- To show an error
End If
Else
WorkingDays = -1 '-- To show an error
End If

exit_workingDays:
Exit Function

err_workingDays:
MsgBox "Error No: " & Err.Number & vbCr & _
"Description: " & Err.Description
Resume exit_workingDays

End Function

Then in the ControlSource of your TextBox put:

=WorkingDays([date raised], [date Responded])

 

[Guest]

Since you need to exclude weekends, the code is necessary. If date raised and
date Responded are both on you form, then it is a pretty simple mater.
I would suggest you put the code in a standard module in case you need to
use it in another form or in a report. Be sure the function is a public
function. You do that in the declaration line of the function. It should be
declared as Public. Here is an example.
Public Function CountDays(dtmStartDate as Date, dtmEndDate As Date) As Integer

Now in your form, you can use the function as the Control Source property of
the control where you want to show the number of days. Please note that you
should not store this number in a table. No calculated value should ever be
stored in a table as long as the data necessary to make the calculation is
already available.

Make the control source of that control:

=CountDays(txtDateRaised, txtDateResponded)

 

[Arvin Meyer [MVP]]

Hi,

I really need some help with the following. I want to add a text box to a
form that displays the amount of days that it has taken to resolve a
query.
For example i have " date raised" and "date Responded" i want the new text
box to show the how many days it has taken from the date raised to date
resolved. Excluding weekends. I have through this forum picked up some
code
for this but i dont know how to link the code to the text box. Can anyone
help? Is code really necessary? or is there another way of doing this.

Yup, code is necessary. But I wrote it for you <g>

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: February 19, 1997
' Modified to allow for holidays May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
'....................................................................
On Error GoTo Err_WorkingDays

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

WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function