Ignore null field in calculation

N

NewSysAdmin

Hello,
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...".

Does anyone have any suggestions? Thank you.
 
M

Marshall Barton

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
 
N

NewSysAdmin

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
 
M

Marshall Barton

NewSysAdmin said:
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.
[snip way more code than I want to unravel]


What do you want to happen when DateClosed is Null?

If you want to use todays date, then the query can use:

NumDaystoComplete: BDateDiff([Date of Call],Nz([Date
closed], Date()))

Or maybe you want to the date of call for date closed:

NumDaystoComplete: BDateDiff([Date of Call],Nz([Date
closed], [Date of Call]))

Or maybe you just want the whole thing to be 0:

NumDaystoComplete: IIf([Date closed] Is Null, 0,
BDateDiff([Date of Call],[Date closed])

Or ???
 
N

NewSysAdmin

Marsh,
I went with the code for making it 0 if Date Closed is No. Then I found
other code to only count the records if that value is > 0. After that I was
able to calculate the average. Thanks again for all your help!

Marshall Barton said:
NewSysAdmin said:
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.
[snip way more code than I want to unravel]


What do you want to happen when DateClosed is Null?

If you want to use todays date, then the query can use:

NumDaystoComplete: BDateDiff([Date of Call],Nz([Date
closed], Date()))

Or maybe you want to the date of call for date closed:

NumDaystoComplete: BDateDiff([Date of Call],Nz([Date
closed], [Date of Call]))

Or maybe you just want the whole thing to be 0:

NumDaystoComplete: IIf([Date closed] Is Null, 0,
BDateDiff([Date of Call],[Date closed])

Or ???
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top