|
Subject:
|
DateDiff?? ( w/o weekends... )
|
|
Posted By:
|
underscore10304
|
Post Date:
|
1/23/2006 9:17:09 AM
|
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 By:
|
mmcdonal
|
Reply Date:
|
1/23/2006 3:29:44 PM
|
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 By:
|
mmcdonal
|
Reply Date:
|
1/23/2006 3:31:14 PM
|
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 By:
|
SerranoG
|
Reply Date:
|
1/23/2006 4:16:30 PM
|
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.
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 By:
|
SerranoG
|
Reply Date:
|
1/23/2006 4:18:40 PM
|
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 By:
|
rgerald
|
Reply Date:
|
1/23/2006 4:52:00 PM
|
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 By:
|
mmcdonal
|
Reply Date:
|
1/24/2006 7:25:54 AM
|
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 By:
|
SerranoG
|
Reply Date:
|
1/24/2006 7:55:01 AM
|
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.
|
