Re: Working Day calculations

That code simply tells you how many working days are between two dates: it
doesn't allow you to add (or subtract) a number of working days to a date,
which is what you appear to want.

Take a look at http://www.mvps.org/access/datetime/date0012.htm at "The
Access Web", or my September, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free from http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"WOODA" <WOODA@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:11FE4CC9-6B71-464C-985E-052912E470B1@xxxxxxxxxxxxxxxx
Hello,
I've been given a project at work which involves using forms and tables in
Access.
I've got the possibility of two different dates being entered into a
field.
I need to calculate a certain amount of working days depending on which
field
the user completes.

I've seen this code;

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************

But I don't know how to incorporate this code into the other bits of code
that I've already got - I'm quite new to all this coding! I've created the
tblholiday and added the holidays to the table.

The input fields i have are called - [date_complaint_received] and
[date_complaint_agreed]. I want to add 10 working days (including
holidays)
onto which ever field is completed.

Any help will be gratefully received!!
Many Thanks - WOODA



.

Relevant Pages

  • Re: Default value of 5 WorkingDays after other control value
    ... > Dim intDayCount as Integer ... > dtmReturnDate = OriginalDAte ... >> Dim intCount As Integer ... >> but it does not use the Holidays table. ...
    (microsoft.public.access.formscoding)
  • RE: Default value of 5 WorkingDays after other control value
    ... EndDate As Date) As Integer ... Dim intDayCount as Integer ... > Dim intCount As Integer ... > but it does not use the Holidays table. ...
    (microsoft.public.access.formscoding)
  • Re: Workdays function with Australian Dates
    ... Web, but as I am in Australia and my date format is dd/mm/yyyy, I have ... found that the dates I put in my holidays table are reversed into American ... ' Inputs: StartDate As Date ... Dim intCount As Integer ...
    (comp.databases.ms-access)
  • data type mismatch error 3464
    ... EndDate As Date) As Integer ... ' Inputs: StartDate As Date ... Dim intCount As Integer ... Dim rst As DAO.Recordset ...
    (microsoft.public.access.queries)
  • Re: Viewing Totals/GroupBy creates Data Mismatch
    ... ' holidays based on tblHolidays. ... Dim WholeWeeks As Variant ... >> Dim intCount As Integer ... >>I have a WorkingDays function that returns number of working days between ...
    (microsoft.public.access.queries)
Loading