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.

Hi,
first - check that you get correct dates into function, then you have to
change this line:

strWhere = "[Holiday] >= #" & format(startDate,"mm\/dd\/yyyy") _
& "# AND [Holiday] <= #" & Format(endDate,"mm\/dd\/yyyy") & "#"

Jet correctly understand dates, passed in mm/dd/yyyy format


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"Will Flanagan" <(E-Mail Removed)> wrote in message
news:8E2ADB9A-440F-4A1D-80AB-(E-Mail Removed)...
> 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.

Acces will always refer to US date format when using date variable in code.
You can SEE Uk format but you should USE US format in code or SQL statements
(all the use the US format for date, is an international convention on any
RDBMS and more general in programming).
Use the format property for the table field to show the date in your desired
format but use the format function in code to have date in US format:
Format(myDate, "\#mm\/dd\/yyyy\#")

rocco


"Will Flanagan" wrote:

> 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.