working days calculation

  • Thread starter Thread starter Jayster22
  • Start date Start date
J

Jayster22

I have tried to use Arvin Meyer's GetBusinessDay function to calculate a due
date from a recieved date. It works fine except for one problem:

The function reads dates from the holiday table as US dates but all my
region settings are in UK Date format. So if I have a holiday date as
31/12/08 the function will not recognise this date as a holiday & incorrectly
calculate the due date.

So my question is how do I get the function to realise my table is in UK
format?
 
Amending this line wherever it occurs:

rst.FindFirst "[HolidayDate] = #" & datStart & "#"

to:

rst.FindFirst "[HolidayDate] = #" & Format(datStart,"mm/dd/yyyy") & "#"

should do it.

Ken Sheridan
Stafford, England
 
As Ken mentions, you need to reformat dates as the US format. This is true
of all SQL as well. You may be interested in this at the Access Web:

http://www.mvps.org/access/datetime/date0005.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Ken Sheridan said:
Amending this line wherever it occurs:

rst.FindFirst "[HolidayDate] = #" & datStart & "#"

to:

rst.FindFirst "[HolidayDate] = #" & Format(datStart,"mm/dd/yyyy") & "#"

should do it.

Ken Sheridan
Stafford, England

Jayster22 said:
I have tried to use Arvin Meyer's GetBusinessDay function to calculate a
due
date from a recieved date. It works fine except for one problem:

The function reads dates from the holiday table as US dates but all my
region settings are in UK Date format. So if I have a holiday date as
31/12/08 the function will not recognise this date as a holiday &
incorrectly
calculate the due date.

So my question is how do I get the function to realise my table is in UK
format?
Click to expand...
Click to expand...
 
Thank you very much it worked a treat

Ken Sheridan said:
Amending this line wherever it occurs:

rst.FindFirst "[HolidayDate] = #" & datStart & "#"

to:

rst.FindFirst "[HolidayDate] = #" & Format(datStart,"mm/dd/yyyy") & "#"

should do it.

Ken Sheridan
Stafford, England

Jayster22 said:
I have tried to use Arvin Meyer's GetBusinessDay function to calculate a due
date from a recieved date. It works fine except for one problem:

The function reads dates from the holiday table as US dates but all my
region settings are in UK Date format. So if I have a holiday date as
31/12/08 the function will not recognise this date as a holiday & incorrectly
calculate the due date.

So my question is how do I get the function to realise my table is in UK
format?
Click to expand...
Click to expand...
 
Thank you Arvin for the links they will be very useful for future
developments.

Arvin Meyer said:
As Ken mentions, you need to reformat dates as the US format. This is true
of all SQL as well. You may be interested in this at the Access Web:

http://www.mvps.org/access/datetime/date0005.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Ken Sheridan said:
Amending this line wherever it occurs:

rst.FindFirst "[HolidayDate] = #" & datStart & "#"

to:

rst.FindFirst "[HolidayDate] = #" & Format(datStart,"mm/dd/yyyy") & "#"

should do it.

Ken Sheridan
Stafford, England

Jayster22 said:
I have tried to use Arvin Meyer's GetBusinessDay function to calculate a
due
date from a recieved date. It works fine except for one problem:

The function reads dates from the holiday table as US dates but all my
region settings are in UK Date format. So if I have a holiday date as
31/12/08 the function will not recognise this date as a holiday &
incorrectly
calculate the due date.

So my question is how do I get the function to realise my table is in UK
format?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top