Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Jun 2003
    Location
    New York, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Spans (Access 2K)

    Greetings! Have a report request that goes beyond my knowledge base. Any guidance is appriciated.

    I want to create a report that will compare the following dates and show time span between. This would only be for records where ID_status=1 or 2

    incident_date : reported_date

    If further_investigation (check box) is checked, then
    prelimrec_date : finalsenttodirector_date

    incident_date : prelimsenttodirector_date BUT IF further_investigation is checked, then
    incident_date : finalsenttodirector_date

    Also would like to create a separate report flagging only those records where ID_status=1 or 2 when the difference between current date & incident_date exceeds 5 business days. I trust there's a way to do this right in the query. yes?

    I welcome your thoughts/suggestions.

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Spans (Access 2K)

    Hi Janie... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Well, I'm not sure I understand your conditions correctly... but this is how I'd set it up...
    First, I would deal with getting the records I want (i.e. the records with ID_status 1 or 2)
    <UL><LI>open a new query in design view...
    <LI>add the table with the records I want...
    <LI>select the fields for the report...
    <LI>in the Criteria section for the ID_status, I'd enter 1 or 2[/list]Then I'd use the DateDiff() function to find the time span between the dates...
    If you look in Access Help you'll see that you can specify that you want the amount of days, weeks, weekdays, months, etc...
    One example could be something like... DaysElapsed: DateDiff("d", [indcident_date], [reported_date])
    This would return a number respresenting the amount of days between the two dates...

    Once you get the correct results for the date difference field(s), you can add criteria specifying "> 5" or something like that...

    Sounds like you will need some nested IIF's to choose the right fields to use, but I'd need a little clarification in order to help with that...
    Hope this gets you started at least....

  3. #3
    Lounger
    Join Date
    Jun 2003
    Location
    New York, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Spans (Access 2K)

    Started with the easy one first. Success! Now have a functioning (properly) query to pull ID_status 1 or 2 that have incident_date greater than 4 days. Also found some info on NETWORKDAYS to help with the business day count.
    I'll try the bigger report over the weekend and keep you posted. Enjoy yours and thanks for the guidance!

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Date Spans (Access 2K)

    One way you can use the Excel ATP NETWORKDAYS function in Access is to set a reference to Microsoft Office Web Components Function Library (MSOWCF.DLL) which should be available if using Office 2K or later. (There is no way to directly set a reference to an Excel add-in like the Analysis ToolPak from Access.) Wrap the NETWORKDAYS function in a public user-defined function, it can then be used in a query. Example:

    Public Function GetNetWorkdays(ByVal StartDate As Date, _
    ByVal EndDate As Date) As Long
    ' Library MSOWCFLib
    ' C:Program FilesMicrosoft OfficeOffice10MSOWCF.DLL
    ' Microsoft Office Web Components Function Library
    Dim atp As New MSOWCFLib.OCATP

    GetNetWorkdays = atp.NETWORKDAYS(StartDate, EndDate)
    Set atp = Nothing

    End Function

    Example of use:
    ? GetNetWorkdays(#7/07/03#,#7/11/03#)
    5
    ? GetNetWorkdays(#7/07/03#,#7/12/03#)
    5
    ? GetNetWorkdays(#7/07/03#,#7/14/03#)
    6

    The query criteria for this would be something like:

    WHERE GetNetWorkdays ([incident_date],Date()) > 5 AND ....

    If you want to take into account holidays, you would need a holiday table (the usual approach) and then modify the function accordingly. For example, you could open a recordset on the table, using start & end dates as criteria, and get a recordcount to adjust the number of days returned by NETWORKDAYS by subtracting number of holidays (if any) found in the date range.

    HTH

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Date Spans (Access 2K)

    PS: Here is example of VBA function to get number of workdays w/o having to reference external library:

    Function GetWorkDays(ByVal StartDate As Date, _
    ByVal EndDate As Date) As Long

    Dim lngWeeks As Long
    Dim tmpDate As Date
    Dim intDays As Integer

    lngWeeks = DateDiff("w", StartDate, EndDate)
    tmpDate = DateAdd("ww", lngWeeks, StartDate)
    intDays = 0

    Do While tmpDate <= EndDate
    If Weekday(tmpDate) <> vbSunday And Weekday(tmpDate) <> vbSaturday Then
    intDays = intDays + 1
    End If
    tmpDate = DateAdd("d", 1, tmpDate)
    Loop

    GetWorkDays = lngWeeks * 5 + intDays

    End Function

    This will provide same results as using NETWORKDAYS function. Again, you would have to modify function if you want to take holidays into account.

    HTH

  6. #6
    Lounger
    Join Date
    Jun 2003
    Location
    New York, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Spans (Access 2K)

    MarkD to the rescue!!
    You must have guessed the question I got today. "Janie, does this report count by business days?" Not yet, was my reply.

    You've given me some useful code. Where do I put the VBA code? or Where do I put the Public User Defined Function for use in a query? Doesn't matter to me which one I use. Is there a difference? Also, I don't know how to do a holiday table.

    Thanks again!

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Date Spans (Access 2K)

    The function needs to be placed in a standard code module (meaning not a class module, which includes form & report modules). Switch to VB Editor (ALT + F11), then select Module from Insert menu. Copy & paste code - recommend use the second function posted, since it does not require an external reference. This is another function along same lines which may be useful in determing workdays - this function will return the next (or previous) workday by x number of workdays:

    Public Function GetNextWorkday(ByVal StartDate As Date, _
    ByVal lngInterval As Long) As Date

    Dim lngWeeks As Long
    Dim lngDays As Long

    If lngInterval = 0 Then
    GetNextWorkday = StartDate

    ElseIf lngInterval > 0 Then
    ' Make sure StartDate is a workday (round down):
    If Weekday(StartDate) = vbSunday Then
    StartDate = StartDate - 2
    ElseIf Weekday(StartDate) = vbSaturday Then
    StartDate = StartDate - 1
    End If

    ' Calculate lngWeeks and lngDays:
    lngWeeks = lngInterval 5 ' Integer division operator
    lngDays = lngInterval - (lngWeeks * 5) ' remainder
    StartDate = StartDate + (lngWeeks * 7)

    ' Account for weekends:
    If (DatePart("w", StartDate) + lngDays) > 6 Then
    StartDate = StartDate + lngDays + 2
    Else
    StartDate = StartDate + lngDays
    End If

    Else ' lngInterval < 0
    lngInterval = lngInterval * -1 ' Make positive & subtract later

    ' Make sure StartDate is a workday (round up):
    If Weekday(StartDate) = vbSunday Then
    StartDate = StartDate + 1
    ElseIf Weekday(StartDate) = vbSaturday Then
    StartDate = StartDate + 2
    End If

    lngWeeks = lngInterval 5 ' Integer division operator
    lngDays = lngInterval - (lngWeeks * 5)
    StartDate = StartDate - (lngWeeks * 7)

    If (DatePart("w", StartDate) - lngDays) < 2 Then
    StartDate = StartDate - lngDays - 2
    Else
    StartDate = StartDate - lngDays
    End If

    End If

    GetNextWorkday = StartDate

    End Function

    If you want the next workday, use positive value for lngInterval argument. For a previous workday, use negative number. Example, using today (7/14/2003) as StartDate:

    ? GetNextWorkday(Date(), 5)
    07/21/2003
    ? GetNextWorkday(Date(), -5)
    07/07/2003

    Attached text file is simple example of a Holidays table, note it lists only the ten "official" Federal holidays recognized by US Gov't. You can import this text file into Access to see how it works. If you need to calculate the number of holidays that fall with a given date range you can use a function like this:

    Public Function GetHolidayCount(ByVal StartDate As Double, _
    ByVal EndDate As Double) As Integer
    On Error GoTo Err_Handler

    Dim rst As ADODB.Recordset
    Dim strMsg As String
    Dim strSQL As String

    strSQL = "SELECT HolidayDate FROM tblHolidays " & _
    "WHERE HolidayDate >=" & StartDate & " " & _
    "AND HolidayDate <=" & EndDate & " " & _
    "ORDER BY HolidayDate;"

    Set rst = New ADODB.Recordset
    rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic

    GetHolidayCount = rst.RecordCount

    rst.Close

    Exit_Sub:
    Set rst = Nothing
    Exit Function
    Err_Handler:
    Select Case Err.Number
    Case 0
    Resume Next
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "GET HOLIDAY COUNT FUNCTION ERROR"
    Resume Exit_Sub
    End Select

    End Function

    You could use this along with function previously posted to take holidays into account when calculating workdays.

    HTH
    Attached Files Attached Files

  8. #8
    Lounger
    Join Date
    Jun 2003
    Location
    New York, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Spans (Access 2K)

    Copied and pased the VBA function to a Class Module named GetWorkdays (thx for placement assistance)
    Now I'm working on the query criteria:
    under incident_date in simple query mode entered the following as criteria:

    WHERE GetWorkdays ([incident_date],Date()) > 5

    I get an error message when leaving the field, "The expression you entered contains invalid syntax. You may have entered an operand without and operator."

    Other options?

    Also, if I create a holiday table called holidays_tbl, how do I reference that in the criteria? It will be a stand alone table, yes?

    Thanks MarkD. Bean counters are the best! I owe you an MGD or Summer Ale.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Spans (Access 2K)

    MarkD wrote
    <hr>The function needs to be placed in a standard code module (meaning not a class module, ...<hr>
    (my emphasis)

    If you put the function in a class module, it will not be recognized in a query.

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Date Spans (Access 2K)

    As noted by HansV, user-defined functions need to be located in standard module, not class module - you can only reference functions in class modules by creating an object variable based on the class definition, which does not apply in query. To use holiday table you need to modify the GetWorkDays function previously posted. (You would not reference holiday table in query directly.) Revised function:

    Public Function GetWorkDays(ByVal StartDate As Date, _
    ByVal EndDate As Date, _
    ByRef CountHolidays As Boolean) As Long

    Dim lngWeeks As Long
    Dim tmpDate As Date
    Dim intDays As Integer

    lngWeeks = DateDiff("w", StartDate, EndDate)
    tmpDate = DateAdd("ww", lngWeeks, StartDate)
    intDays = 0

    Do While tmpDate <= EndDate
    If Weekday(tmpDate) <> vbSunday And Weekday(tmpDate) <> vbSaturday Then
    intDays = intDays + 1
    End If
    tmpDate = DateAdd("d", 1, tmpDate)
    Loop

    If CountHolidays = True Then
    GetWorkDays = lngWeeks * 5 + intDays - GetHolidayCount(StartDate, EndDate)
    Else
    GetWorkDays = lngWeeks * 5 + intDays
    End If

    End Function

    Example of use, with or w/o holiday count:

    ? GetWorkDays(#1/1/2003#,#12/31/2003#,False)
    261
    ? GetWorkDays(#1/1/2003#,#12/31/2003#,True)
    251

    This indicates there are 261 workdays in 2003, or 251 not counting the 10 "official" holidays in the table I'm using. Note use of GetHolidayCount function (bold) - this is function posted in previously reply, you'd need to modify the SQL in this function to reflect the field & table names you decide to use for your holiday table.

    HTH

  11. #11
    Lounger
    Join Date
    Jun 2003
    Location
    New York, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Spans (Access 2K)

    MarkD,
    Was my typo in my message. I put the code in the right module (standard). I've sent you my front end via mail to your Yahoo! address.

    What's the HTH mean??? ;-)

    Thanks!

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Date Spans (Access 2K)

    Excuse me for butting in.

    HTH means Hope This Helps.

  13. #13
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Date Spans (Access 2K)

    jcrew:

    I got yr FE .mdb via e-mail, as noted, since all tables are linked, I'd need a stripped down copy of the BE .mdb file (w/the tables) to be able to resolve problem with query. There's no way to test a query w/o any tables. Recommend make a test copy of actual BE db w/some sample data, no real names or sensitive data, & fwd to my Yahoo e-mail.

    I did see one thing you need to fix, which may be causing problem: You named module "GetWorkdays", which is same name as the function. VBA does not like it when a procedure (sub or function) has same name as a code module, and will refuse to cooperate. Recommend rename module so it does not conflict with function name or any other VBA reserved word. You can rename it "modFunctions" or "Module1" or anything else that does not cause a conflict. That may fix problem you're having using function in a query.

    HTH

  14. #14
    Lounger
    Join Date
    Jun 2003
    Location
    New York, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Spans (Access 2K)

    MarkD,
    Sent you a zipped copy of the BE. Changed the name of the module to Module1 per your suggestions. The query for four_day_query in the FE sent is kind of hosed. Pulled the original from a backup copy. Attached in a zip file to this message for you to see and/or swap out.

    Thought HTH might be Hail to The Highness.... <img src=/S/clown.gif border=0 alt=clown width=15 height=15>

    Thanks again!
    Attached Files Attached Files

  15. #15
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Date Spans (Access 2K)

    Looked at four_day_query. Modified query SQL looks like this:

    SELECT incident_maintbl.OMRDD_incidentno, incident_maintbl.incident_date, incident_maintbl.reported_date, incident_maintbl.notified_date, incident_maintbl.prelimrec_date, incident_maintbl.incidentrec_date, incident_maintbl.consumer_firstname, incident_maintbl.consumer_lastname, incident_maintbl.ID_progresp, incident_maintbl.ID_incidentcat, incident_maintbl.ID_typeincident, incident_maintbl.ID_abusecat, incident_maintbl.ID_abusetype, incident_maintbl.prelim_sufficent, incident_maintbl.further_investigation, incident_maintbl.ID_status, incident_maintbl.comments, incident_maintbl.ID_investigator, incident_maintbl.investigatorassigned_date
    FROM incident_maintbl
    WHERE (((incident_maintbl.ID_progresp) In (22,42,21,43,44,45,46,47,27)) AND ((incident_maintbl.ID_status)=1 Or (incident_maintbl.ID_status)=2) AND ((GetWorkDays([incident_date],Date(),True))>5));

    Changes highlighted in BOLD. Note that I simplified criteria for ID_progresp field by replacing all those OR's with an IN clause. For the workdays, I added a calculated field that is used for WHERE criteria only, using GetWorkDays function. The query will return any records where incident date was at least one week ago or earlier (and other criteria are met). Note that GetWorkDays function counts both StartDate and EndDate. For example, using today's date and one week ago:

    ? GetWorkDays(#07/09/2003#,#07/16/2003#,True)
    6

    Function returns 6, because both today (Weds) & last Weds are counted. So if you run query today, and incident date was last Weds, the incident will be included in query results. Modify query if necessary. Note if NOTusing holiday table need to replace "True" with "False" for 3rg arg of GetWorkDays function.

    I will fwd you modified copy of FE (including holiday table & functions) via e-mail so you can see how it works.

    HTH

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •