How to prevent #Error appearing in Query

[Kev86]

I am trying to create a query that sums the working days between two dates, however these dates don't always have information in the fields so where they are blank i want the resulting sum to return 0.

However when i try to do this i get a #Error appearing in the field. As soon as i add dates to the fields it's trying to sum it provides a result but if they are blank i get the #Error.



The query is a new column and the code is "Working Days In Consents: Weekdays([3rdPartyReqSent],[3rdPartyCleared])-1"

I know you can use NZ to get the field to recognise a null but i am struggling to know how to add this to the following code "Working Days In Consents: Weekdays([3rdPartyReqSent],[3rdPartyCleared])-1".

Any help would be much appreciated.

[pbaldy]

Try this untested effort:

IIf(IsDate([3rdPartyReqSent]) AND IsDate([3rdPartyCleared]),Weekdays([3rdPartyReqSent],[3rdPartyCleared])-1,0)

[Bob Fitz]

I am trying to create a query that sums the working days between two dates, however these dates don't always have information in the fields so where they are blank i want the resulting sum to return 0.

However when i try to do this i get a #Error appearing in the field. As soon as i add dates to the fields it's trying to sum it provides a result but if they are blank i get the #Error.

The query is a new column and the code is "Working Days In Consents: Weekdays([3rdPartyReqSent],[3rdPartyCleared])-1"

I know you can use NZ to get the field to recognise a null but i am struggling to know how to add this to the following code "Working Days In Consents: Weekdays([3rdPartyReqSent],[3rdPartyCleared])-1".

Any help would be much appreciated.

AFAIK there is no "Weekdays()" function. Perhaps you can post a copy of your db

[pbaldy]

Me neither, but it works when provided dates. Perhaps a custom function.

[madpiet]

I am trying to create a query that sums the working days between two dates, however these dates don't always have information in the fields so where they are blank i want the resulting sum to return 0.

However when i try to do this i get a #Error appearing in the field. As soon as i add dates to the fields it's trying to sum it provides a result but if they are blank i get the #Error.

The query is a new column and the code is "Working Days In Consents: Weekdays([3rdPartyReqSent],[3rdPartyCleared])-1"

I know you can use NZ to get the field to recognise a null but i am struggling to know how to add this to the following code "Working Days In Consents: Weekdays([3rdPartyReqSent],[3rdPartyCleared])-1".

Any help would be much appreciated.

One really flexible way of dealing with working days (especially if your working days don't follow a standard M-F schedule etc) is to use a Calendar table, that has columns like Date, DayName, MonthNumber, MonthName, IsHoliday etc -- whatever columns you need to make your queries work.

Then you can use an extra simple OUTER JOIN pattern.

SELECT Calendar.TheDate, SUM(fact.NumericColumn)
FROM Calendar LEFT JOIN fact ON Calendar.TheDate = fact.TheDate
WHERE Calendar.DayOfWeek NOT ('Saturday','Sunday')
GROUP BY Calendar.TheDate.

Since you're querying a table and filtering it (because all the date math stuff is already stored in the table) it would be fast. it's only 365,240 records for 100 years, so relatively small compared to a normal fact-like table.

[Kev86]

AFAIK there is no "Weekdays()" function. Perhaps you can post a copy of your db

Yes Weekdays is a Function, please see the code below which relates to that function....

Code: Public Function Weekdays(ByRef startDate As Date, _ ByRef endDate As Date _
) As Long
' 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 "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Weekdays"
Resume Weekdays_Exit
End Function

[Kev86]

Hi Paul,

This worked a treat, thank you.

[pbaldy]

Hi Paul,

This worked a treat, thank you.

Happy to help!