Thank you for your correction. Sometimes I don't use the correct terms.
You're right, the problem is within the BDateDiff function. I am getting a
#Error for NumDaystoComplete when the date closed is blank (which it is until
the record is closed).
I looked through the code for BDateDiff, and was wondering if you could help
me as to where to put in the Nz code (unless there is a better way to handle
nulls. Please see code below. Thank you for any additional help you can
provide.
Public Function BDateDiff(StartDate As Date, EndDate As Date, Optional
aHolidays As Variant) As Integer
' Copyright (c) 2002, TAG Consulting
' All rights reserved
' The following routine may be used in any application so long as
' the copyright notice remains intact
Dim AdjEndDate As Date
Dim AdjStartDate As Date
Dim bEndLater As Boolean
Dim bHolidays As Boolean
Dim EndDays As Integer
Dim I As Integer
Dim MondayStart As Date
Dim MondayEnd As Date
Dim StartDays As Integer
' Determine if an array of holiday dates were included in the parameter list
If IsMissing(aHolidays) Then
bHolidays = False
ElseIf Not IsArray(aHolidays) Then
bHolidays = False
Else
bHolidays = True
End If
' Determine the direction of the calculation
Select Case DateDiff("d", StartDate, EndDate)
Case Is > 0 ' Start date earlier than end date
' For weekends, move back to the previous Friday.
Select Case Weekday(StartDate)
Case 7
AdjStartDate = DateAdd("d", -1, StartDate)
Case 1
AdjStartDate = DateAdd("d", -2, StartDate)
Case Else
AdjStartDate = StartDate
End Select
AdjEndDate = EndDate
bEndLater = True
Case Is < 0 ' End date earlier than start date
' For weekends, move up to the next Monday.
Select Case Weekday(EndDate)
Case 7
AdjStartDate = DateAdd("d", 2, EndDate)
Case 1
AdjStartDate = DateAdd("d", 1, EndDate)
Case Else
AdjStartDate = EndDate
End Select
AdjEndDate = StartDate
bEndLater = False
Case 0 ' Trivial calculation averted
BDateDiff = 0
Exit Function
End Select
StartDays = Weekday(AdjStartDate) - 2
MondayStart = DateAdd("d", StartDays * -1, AdjStartDate)
EndDays = Weekday(AdjEndDate) - 2
MondayEnd = DateAdd("d", -1 * EndDays, AdjEndDate)
BDateDiff = (5 / 7) * DateDiff("d", MondayStart, MondayEnd) - StartDays +
EndDays
If bHolidays Then
For I = LBound(aHolidays, 1) To UBound(aHolidays, 1)
If IsDate(aHolidays(I)) Then
If DateDiff("d", AdjStartDate, aHolidays(I)) >= 0 And DateDiff("d",
AdjEndDate, aHolidays(I)) <= 0 Then
BDateDiff = BDateDiff - 1
End If
End If
Next I
End If
If Not bEndLater Then
BDateDiff = BDateDiff * -1
End If
End Function
__________________________________________________________
Marshall Barton said:
NewSysAdmin said:
I'm trying to use sum function to sum values in a field called
"NumDaystoComplete" to display on a report. This field is calculated in a
query with the following syntax:
NumDaystoComplete: BDateDiff([Date of Call],[Date closed])
BDateDiff is a module which calculates the number of business days between 2
dates.
Here is my syntax for the control on the report where I want to display the
sum of NumDaystoComplete: =Sum(Nz([NumDaystoComplete],0)) .
It calculates OK as long as there is a value in NumDaystoComplete. I do
have some blanks, though, which is what I thought using "Nz" would help with.
I get the error "this expression is typed incorrectly, or is too complex to
evaluate...".
FYI - the aggregate functions ignore Null values so you do
not have a reason to use Nz here. The text box expression:
=Sum(NumDaystoComplete)
should work.
If not, then the problem is probably in the BDateDiff code,
especially in how it deals with a Null in either of the two
arguments.
Note: you said "BDateDiff is a module", which is incorrect.
If BDateDiff really is a module name, you should get a
different error. The way you are using it, BDateDiff is the
name of a **function** that resides in a standard module
named something other than BDateDiff
