Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old January 23rd, 2006, 10:17 AM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to underscore10304
Default DateDiff?? ( w/o weekends... )

Hi All - hope I am going through the correct channels to get my post heard...
This is a highly unoriginal query - I'm aware that there have been previous posts relating to this but any guidance in this area will be greatly appreciated. My Access development at this stage is limited to docmds and not much else!
I have created a database for our company's HR department that they are pushing to 'go live' imminently. Within the database are two expressions ( simple formulas that subtract from a set no. of holidays, e.g. 20 ) - one calculates days taken and the other; days remaining.
HOWEVER, I have not accounted for weekends or holidays.
Is there a ( straightforward ) method I could implement that'll do this for me?
As I mentioned my VBA is virtually non-existent and I tend to get intimidated by long, scary bits of code...
Can anyone help?

  #2 (permalink)  
Old January 23rd, 2006, 04:29 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I recently had to do this like this... as always, brute force with me. It's my coding background.

'-----------------------
'Declare Variables
Dim rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim StartDate
Dim EndDate
Dim DateRange As Integer
Dim CheckDate
Dim WorkDay As Integer
Dim i As Integer
Dim DayCount As Integer
'-----------------------

'-----------------------
'Clear out old data
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELETETempTechOpenPerDay"
DoCmd.SetWarnings True
'-----------------------

'-----------------------
'Set Connection for the first recordset and gather data
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "SELECT * FROM qryTEMPTechOpenPerDay"
'-----------------------

'-----------------------
'Set connection and open second recordset to receive data
Set rs1 = New ADODB.Recordset
    rs1.ActiveConnection = CurrentProject.Connection
    rs1.CursorType = adOpenDynamic
    rs1.LockType = adLockOptimistic
    rs1.Open "SELECT * FROM tblTEMPTechOpenPerDay"
'-----------------------

'-----------------------
'Start into the first recordset...
rs.MoveFirst
Do Until rs.EOF

    ' take start date variable here from rs
    StartDate = rs.Fields(2) 'DateAcknow
    'take end date variable here
    EndDate = rs.Fields(3) 'DateResolv

    'Take the number of days between the start date and end Date
    DateRange = Abs(DateDiff("d", StartDate, EndDate))

    'Set up counters and variables
    i = 1
    DayCount = 1
    CheckDate = StartDate

    'Loop through data in first recordset, and record
    'dates in second recordset
    Do Until i = DateRange + 1
        WorkDay = DatePart("w", CheckDate)
            If WorkDay <> 1 And WorkDay <> 7 Then
                rs1.AddNew
                rs1("CallID") = rs.Fields(0)
                rs1("Assignee") = rs.Fields(1)
                rs1("DateOpen") = CheckDate
                rs1.Update
            End If
        CheckDate = DateAdd("d", DayCount, StartDate)
        DayCount = DayCount + 1
        i = i + 1
    Loop

    'Check the last date in the range by itself
    WorkDay = DatePart("w", EndDate)
    If WorkDay <> 1 Or WorkDay <> 7 Then
        rs1.AddNew
        rs1("CallID") = rs.Fields(0)
        rs1("Assignee") = rs.Fields(1)
        rs1("DateOpen") = EndDate
        rs1.Update
    End If
rs.MoveNext
Loop
'-----------------------

HTH


mmcdonal
  #3 (permalink)  
Old January 23rd, 2006, 04:31 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

This gave me a discrete record for every day a tech worked, since I needed to aggregate them in other ways. I created this table and then ran a bunch of queries off it for historic reports, but you get the idea.



mmcdonal
  #4 (permalink)  
Old January 23rd, 2006, 05:16 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

This should do it. Assuming you have a start date and an end date typed in two textboxes called txtStart and txtEnd respectively; and the total is in a textbox called txtTotal.

Code:
Dim intTotalDays As Integer, intWeekendDays As Integer, intFactor As Integer

intTotalDays = DateDiff("d", Me.txtStart, Me.txtEnd) + 1
intWeekendDays = (intTotalDays \ 7) * 2

Select Case Weekday(Me.txtStart)
    Case 1
        intFactor = 1
    Case 7
        intFactor = 2
    Case Else
        intFactor = 0
End Select

intTotalDays = intTotalDays - intWeekendDays - intFactor
Me.txtTotal = intTotalDays
Now if you're NOT including the end date as a day to count, remove the + 1 after the DateDiff function.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
  #5 (permalink)  
Old January 23rd, 2006, 05:18 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Note that the intTotalDays \ 7 is not a typo. It should be backslash \ and not forward slash /. It's not normal division, but for the life of me I cannot remember what it's called.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
  #6 (permalink)  
Old January 23rd, 2006, 05:52 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The "\" operator performs integer division. There is no remainder or fractional part. For example 5 \ 2 = 2, on the other hand 5 / 2 = 2.5



Rand
  #7 (permalink)  
Old January 24th, 2006, 08:25 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Excellent. Well, as laways, I learned quite a bit.

I was worried about the integer division, but I figured that casing the variable would remove the remainder and round the result. I didn't know about the "\" notation.

And the solution is much more elegant. However in my case I did need to mark a place for every day of the year that an assignment was open so that I could average assignments per tech per day.

Anywho, thanks again for the elegant solution, SerranoG! (even though this wasn't my post.) :)


mmcdonal
  #8 (permalink)  
Old January 24th, 2006, 08:55 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Thanks, Rand, for the name; and thanks, MMcDonnal for the compliment. Underscore, you can put that code in a button's On Click Event or txtEnd's After Update Event, etc.


Similar Threads
Thread Thread Starter Forum Replies Last Post
DateDiff to exclude weekends and holidays dbartelt Access 2 April 12th, 2008 05:14 PM
DateDiff zahyea .NET Framework 1.x 0 October 16th, 2007 04:38 AM
datediff ebburks Access 2 July 28th, 2006 06:58 PM
Date diff excluding holidays and weekends Altonjain Classic ASP Professional 0 February 9th, 2006 12:56 AM
Using DateDiff iancrabtree Access 2 November 27th, 2005 07:33 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.