CalcworkDays function in query - add criteria

[lbgtp]

Hi all,



I have a query that is showing order info between a start and end date that a user inputs from a form. Everything is working correctly with the CalcWorkDay module and I'm just trying to limit the results in the query.

I only want to see orders that took longer than 1 day to ship, so I added ">1" in the criteria row for my DaystoShip expression. It errors out.

DaystoShip: CalcWorkDays([Order Date],[Ship Date])
expression
Criteria: >1 - this gives me an error saying too complex.

How can I get this to work?

Here's the SQL view:

SELECT tblOutbound_Orders.[Order Number], tblOutbound_Orders.[Order Date], tblOutbound_Orders.[SEC ID], tblOutbound_Orders.[Order Priority], tblOutbound_Orders.[Ship Date], tblOutbound_Orders.Status, CalcWorkDays([Order Date],[Ship Date]) AS DaystoShip, DCount([Order Priority],"qryTotal_HP_Orders") AS TotalHPCount
FROM tblOutbound_Orders
GROUP BY tblOutbound_Orders.[Order Number], tblOutbound_Orders.[Order Date], tblOutbound_Orders.[SEC ID], tblOutbound_Orders.[Order Priority], tblOutbound_Orders.[Ship Date], tblOutbound_Orders.Status
HAVING (((tblOutbound_Orders.[Order Date]) Between [Forms]![frmShipments]![txtStartDate] And [Forms]![frmShipments]![txtEndDate]) AND ((tblOutbound_Orders.[Order Priority])<4) AND ((CalcWorkDays([Order Date],[Ship Date]))>1))
ORDER BY tblOutbound_Orders.[Order Number], tblOutbound_Orders.[Order Date];

[lbgtp]

Here is the CalcWorkDays code:

Public Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
 
On Error GoTo CalcWorkDays_Error
 
'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, vbSaturday) + _
DateDiff("ww", dtmStart, dtmEnd, vbSunday))
 
'Fix bug found where months starting on Saturday or Sunday always over state by one day
If Weekday(dtmStart, vbMonday) > 5 And Day(dtmStart) = 1 Then
CalcWorkDays = CalcWorkDays - 1
End If
 
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHoliday", "[holdate]between #" & dtmStart & "# And #" & dtmEnd & "#")
 
CalcWorkDays_Exit:
 
On Error Resume Next
Exit Function
 
CalcWorkDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
 
GoTo CalcWorkDays_Exit
End Function

[lbgtp]

Can anyone help?