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?

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

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

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.

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

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

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

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

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.