Amendment to code 1

[DajOne]

This is a functionnality that a good soul posted on this board a while ago..

I would like to amend the code to provide for either/or BegDate = DateValue(BegDate)EndDate = DateValue(EndDate) to be nil/void and not come up with an error, just a blank field.. . I tried if statements without success. The work_days code is intended to be used in a form or a report.

Thanks in advance

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function

 

[PHV]

something like this ?
If Not (IsDate(BegDate) And IsDate(EndDate)) Then
Work_Days = 0
Exit Function
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DajOne]

Thanks for the code.. Where should I insert it? I have yet to learn how to program..

 

[DajOne]

I tried inserting the code at several locations without success

 

[Golom]

Try inserting it here

Function Work_Days(ByVal BegDate As Variant, 
                   ByVal EndDate As Variant) As Integer
   [COLOR=black cyan]' Note that this function does not account for holidays.[/color]
      Dim WholeWeeks As Integer
      Dim DateCnt As Date
      Dim EndDays As Integer
[COLOR=red]
      If Not (IsDate(BegDate) And IsDate(EndDate)) Then
         Work_Days = 0
      Else
[/color]
         BegDate = DateValue(BegDate)
         EndDate = DateValue(EndDate)
         WholeWeeks = DateDiff("w", BegDate, EndDate)
         DateCnt = DateAdd("ww", WholeWeeks, BegDate)
         EndDays = 0
         Do While DateCnt < EndDate
            Select Case WeekDay(DateCnt)
               Case 1, 7: EndDays = EndDays + 1
            End Select
            DateCnt = DateAdd("d", 1, DateCnt)
         Loop
         Work_Days = WholeWeeks * 5 + EndDays
[COLOR=red]
      End If
[/color]
End Function

because you are returning an Integer you must return a numeric value (not a blank). If you want to return a blank then use Variant as the Function type.

 

[DajOne]

Thank you very much, now I see the logic..

Thanks again..