Re: Workdays function with Australian Dates

I am living in Holland. We also use dd/mm/yyyy
So I *always* use the lngvalue of dates. ==>>Never have any problems again.
I did have similar issues indeed...

So StartDate would become Clng(StartDate) and so on.

HTH
Arno R


"Dixie" <dixie@xxxxxxxxxxx> schreef in bericht news:121h2ahfuvnoe9f@xxxxxxxxxxxxxxxxxxxxx
It is the holiday dates in the table that are causing the problem. They are
not used in a query.

The problem is that if 11th March 2006 is a holiday, in my table of
holidays, I have 11/03/2006. When the code runs that deducts the holidays
from the list of workdays, it interprets this date as 3rd November 2006 -
3/11/2006.

I guess I could enter all the holiday dates in American format, but this
will inevitably cause problems when others try to enter them as they are not
used to dates in the American format.

dixie

"Jeff" <jeff.pritchard@xxxxxxxxxxxx> wrote in message
news:4418847c$0$23317$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Format you date so -

Format(StartDate, "mm/dd/yyyy")

wherever you use a date in a query for comparison, and add a day so

DateAdd("d",1, StartDate)

This should help.

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
http://www.asken.com.au

"Dixie" <dixie@xxxxxxxxxxx> wrote in message
news:121grcql52u11d@xxxxxxxxxxxxxxxxxxxxx
I am trying to calculate the number of workdays between two dates with
regards to holidays as well. I have used Arvin Meyer's code on the Access
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
dates. So, the wrong holiday dates are subtracted from the total workdays
between the start and end dates. Is there an easy fix for this?

******Code follows******

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******End Code******





.

Relevant Pages

  • 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)
  • Re: Working Day calculations
    ... ' Inputs: StartDate As Date ... ' Note that this function has been modified to account for holidays. ... Dim intCount As Integer ... Dim rst As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Workdays Function : Repost
    ... which includes provision for holidays (I also had to select Microsoft DAO 3.6 ... EndDate As Date) As Integer ... ' Inputs: StartDate As Date ... Dim intCount As Integer ...
    (microsoft.public.access.formscoding)
  • RE: Calculating Holidays
    ... in removing statutory holidays. ... DtmEnd As Date) As Integer ... StartDate ... Dim intCount As Integer ...
    (microsoft.public.access.modulesdaovba)
  • Working Day calculations
    ... ' Inputs: StartDate As Date ... ' Note that this function has been modified to account for holidays. ... Dim intCount As Integer ... Dim rst As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)