Access Report Error

[SunniOne]

Hi All,

I am using the following module to calculate networkdays in Access:

Option Compare Database
Public Function Weekdays(ByRef startDate As Date, _
ByRef endDate As Date _
) As Integer
' Returns the number of weekdays in the period from startDate
' to endDate inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Sunday and
' do not total two per week in number, this function will
' require modification.
On Error GoTo Weekdays_Error

' The number of weekend days per week.
Const ncNumberOfWeekendDays As Integer = 2

' The number of days inclusive.
Dim varDays As Variant

' The number of weekend days.
Dim varWeekendDays As Variant

' Temporary storage for datetime.
Dim dtmX As Date

' If the end date is earlier, swap the dates.
If endDate < startDate Then
dtmX = startDate
startDate = endDate
endDate = dtmX
End If

' Calculate the number of days inclusive (+ 1 is to add back startDate).
varDays = DateDiff(Interval:="d", _
date1:=startDate, _
date2:=endDate) + 1

' Calculate the number of weekend days.
varWeekendDays = (DateDiff(Interval:="ww", _
date1:=startDate, _
date2:=endDate) _
* ncNumberOfWeekendDays) _
+ IIf(DatePart(Interval:="w", _
Date:=startDate) = vbSunday, 1, 0) _
+ IIf(DatePart(Interval:="w", _
Date:=endDate) = vbSaturday, 1, 0)

' Calculate the number of weekdays.
Weekdays = (varDays - varWeekendDays)

Weekdays_Exit:
Exit Function

Weekdays_Error:
Weekdays = -1
MsgBox "" & Err.Number & "" & Err.Description, _
vbCritical, "Weekdays"
Resume Weekdays_Exit
End Function

​

The problem is that this code produces null values which is preventing me from calculating the average of the network days in the report I need to create. The error message is "Data type mismatch in criteria expression".

So frustrated...

Can anyone help me change the code for error messages so that the null values are addressed and I can calculate the average?

Thanks in advance!

 

[SydneyGeek]

You could use the NZ() function to deal with nulls in the dates.

Denis

 

[SunniOne]

Sorry to ask, but what exactly would that look like? I am really new to this (this is my first Access report) and am using my Excel knowledge and Google to hack my way through.

 

[SydneyGeek]

Say you have Nulls in MyField, and you want to sum / average those values.

You could do something like =Sum(NZ([MyField],0))
which converts all Nulls to 0. To average them you don't want to convert Null to zero because that will skew the count so you could try:
=SUM(NZ([MyField],0))/SUM(Iif(IsNull([MyField]),0,1))

Denis

 

[SunniOne]

Thanks, SydneyGeek. This is very helpful.

Another question, would I edit the code that calculates the number of total days, the number of weekend days, or the number of weekdays with this null value formula? Or do I edit all of them?

So, the number of weekdays formula would read:

Weekdays = (NZ(varDays - varWeekendDays), 0)/(Iif(IsNull (varDays - varWeekendDays),0,1))

??
Thanks,
S-