Variation on the WorkingDays2 theme

[PeteyP]

I have studied the many WorkingDays2 examples and discussions posted in the
past, but I could use some more help.

In my form, I use three types of date fields described in A, B, & C below.

A. I have a calculated field named [PlanD1]. It is calculated with a
lookup: =DLookUp("[DLA]![One]","DLA","[Dline]=[Caselog]![deadline]"), and it
works, until add run code to calculcate item C below.

B. I also have a manually entered date field called [ActD1].

C. I want the third field, [Var1] to show the calculcated difference in
working days between [PlanD1] and [ActD1], whether the answer is positive or
negative. I am trying to use my variation of WorkingDays2 (see below) to
work this calculcation. However, when I open the form, I get the #Name?
error in [Var1] and #Error comes up in [PlanD1].

I know it's time for me to ask my boss if I may buy "Visual Basic for
Dummies" but until he consents, could someone please help me with this
challenge. Here is the code:

'....................................................................
' Name: WorkingDays2 (variation)
' Inputs: PlanD1 As Date
' ActD1 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(PlanD1) < vbSunday And Weekday(StartDate) < vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

PlanD1 = PlanD1 + 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

Private Sub Days_BeforeUpdate(Cancel As Integer)
Use = WorkingDays2([PlanD1 as Date], [ActD1 as Date], [Days as Integer])

Private Sub Form_Load()

End Sub

 

[Douglas J Steele]

When you call a function, you don't include the "As <datatype>" parts.

Since PlanD1 and ActD1 are fields on your form, you should refer to them as
Me!PlanD1 and Me!ActD1.

You're missing the declaration line for the WorkingDays2 function and I
couldn't see it on Arvin's site, but I would expect it to only have 2
variables in its declaration, something like:

Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer

In that, StartDate and EndDate are placeholders for the values that are
being passed to the function: you do not need to change them to the names of
the values you intend to pass to the functions, as the comment implies you
have done.

What is Days?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have studied the many WorkingDays2 examples and discussions posted in the
past, but I could use some more help.

In my form, I use three types of date fields described in A, B, & C below.

A. I have a calculated field named [PlanD1]. It is calculated with a
lookup: =DLookUp("[DLA]![One]","DLA","[Dline]=[Caselog]![deadline]"), and it
works, until add run code to calculcate item C below.

B. I also have a manually entered date field called [ActD1].

C. I want the third field, [Var1] to show the calculcated difference in
working days between [PlanD1] and [ActD1], whether the answer is positive or
negative. I am trying to use my variation of WorkingDays2 (see below) to
work this calculcation. However, when I open the form, I get the #Name?
error in [Var1] and #Error comes up in [PlanD1].

I know it's time for me to ask my boss if I may buy "Visual Basic for
Dummies" but until he consents, could someone please help me with this
challenge. Here is the code:

'....................................................................
' Name: WorkingDays2 (variation)
' Inputs: PlanD1 As Date
' ActD1 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(PlanD1) < vbSunday And Weekday(StartDate) < vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

PlanD1 = PlanD1 + 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

Private Sub Days_BeforeUpdate(Cancel As Integer)
Use = WorkingDays2([PlanD1 as Date], [ActD1 as Date], [Days as Integer])

Private Sub Form_Load()

End Sub