Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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?

Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 08:55 AM.


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