Results 1 to 3 of 3
  1. #1
    lbgtp is offline Novice
    Windows XP Access 2000
    Join Date
    Dec 2010
    Posts
    5

    CalcworkDays function in query - add criteria

    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:
    Code:
    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];

  2. #2
    lbgtp is offline Novice
    Windows XP Access 2000
    Join Date
    Dec 2010
    Posts
    5
    Here is the CalcWorkDays code:
    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

  3. #3
    lbgtp is offline Novice
    Windows XP Access 2000
    Join Date
    Dec 2010
    Posts
    5
    Can anyone help?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query Criteria
    By Texaine in forum Queries
    Replies: 1
    Last Post: 01-24-2018, 02:36 PM
  2. Replies: 4
    Last Post: 11-19-2010, 07:21 PM
  3. Query Criteria
    By Alex Motilal in forum Queries
    Replies: 2
    Last Post: 10-06-2010, 08:20 AM
  4. Query Criteria
    By MrMitch in forum Queries
    Replies: 0
    Last Post: 03-31-2009, 02:25 PM
  5. Query Criteria
    By jena in forum Access
    Replies: 1
    Last Post: 08-03-2008, 04:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums