W
Will Flanagan
Hi Folks,
I downloaded a database off the MSDN website (web
address:http://msdn.microsoft.com/en-us/library/dd327646.aspx). It
calculates the number of working days and takes off any holidays which are
stored in a separate table. It works fine, but the only problem is that
when I enter dates in the holiday table, it reads it as the US format of
m/d/yy instead of UK d/m/yy. I have set the regional settings to UK
time/date so my dates in the Holidays table are displayed as UK, but I have
to remember to enter the dates in US order, for the system to work. (I know
this will cause confusion for users later on).
The problematic code is:
Public Function Workdays(ByRef startDate As Date, _
ByRef endDate As Date, _
Optional ByRef strHolidays As String = "Holidays" _
) As Integer
' Returns the number of workdays between startDate
' and endDate inclusive. Workdays excludes weekends and
' holidays. Optionally, pass this function the name of a table
' or query as the third argument. If you don't the default
' is "Holidays".
On Error GoTo Workdays_Error
Dim nWeekdays As Integer
Dim nHolidays As Integer
Dim strWhere As String
' DateValue returns the date part only.
startDate = DateValue(startDate)
endDate = DateValue(endDate)
nWeekdays = Weekdays(startDate, endDate)
If nWeekdays = -1 Then
Workdays = -1
GoTo Workdays_Exit
End If
strWhere = "[Holiday] >= #" & startDate _
& "# AND [Holiday] <= #" & endDate & "#"
' Count the number of holidays.
nHolidays = DCount(Expr:="[Holiday]", _
Domain:=strHolidays, _
Criteria:=strWhere)
Workdays = nWeekdays - nHolidays
Workdays_Exit:
Exit Function
Workdays_Error:
Workdays = -1
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Workdays"
Resume Workdays_Exit
End Function
The dates are stored in the [Holiday] field of the Holidays table.
I am sure that there is something really simple I need to do in this Visual
Basic code to make sure it reads all dates as UK, but I am completely stuck!!
I didn't know if anyone would have any ideas?
Many thanks,
Will.
I downloaded a database off the MSDN website (web
address:http://msdn.microsoft.com/en-us/library/dd327646.aspx). It
calculates the number of working days and takes off any holidays which are
stored in a separate table. It works fine, but the only problem is that
when I enter dates in the holiday table, it reads it as the US format of
m/d/yy instead of UK d/m/yy. I have set the regional settings to UK
time/date so my dates in the Holidays table are displayed as UK, but I have
to remember to enter the dates in US order, for the system to work. (I know
this will cause confusion for users later on).
The problematic code is:
Public Function Workdays(ByRef startDate As Date, _
ByRef endDate As Date, _
Optional ByRef strHolidays As String = "Holidays" _
) As Integer
' Returns the number of workdays between startDate
' and endDate inclusive. Workdays excludes weekends and
' holidays. Optionally, pass this function the name of a table
' or query as the third argument. If you don't the default
' is "Holidays".
On Error GoTo Workdays_Error
Dim nWeekdays As Integer
Dim nHolidays As Integer
Dim strWhere As String
' DateValue returns the date part only.
startDate = DateValue(startDate)
endDate = DateValue(endDate)
nWeekdays = Weekdays(startDate, endDate)
If nWeekdays = -1 Then
Workdays = -1
GoTo Workdays_Exit
End If
strWhere = "[Holiday] >= #" & startDate _
& "# AND [Holiday] <= #" & endDate & "#"
' Count the number of holidays.
nHolidays = DCount(Expr:="[Holiday]", _
Domain:=strHolidays, _
Criteria:=strWhere)
Workdays = nWeekdays - nHolidays
Workdays_Exit:
Exit Function
Workdays_Error:
Workdays = -1
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Workdays"
Resume Workdays_Exit
End Function
The dates are stored in the [Holiday] field of the Holidays table.
I am sure that there is something really simple I need to do in this Visual
Basic code to make sure it reads all dates as UK, but I am completely stuck!!
I didn't know if anyone would have any ideas?
Many thanks,
Will.