I have some functions to calculate the working days in a given period. This includes a table that is queried to calculate the number of public holidays that don’t occur on a weekend.
If I test the function using the intermediate window, it works fine. However, when I pass the dates from the code attached to my form, the results are inaccurate.
You will notice my dates are in Australian format. Everything works fine using the Australian date format except the passing of the dates from a variable to the PublicHolidayCount function. I have found I need to reformat the dates in my form code to US date format to achieve an accurate result.
Can someone tell me why? Here is my table: - HolidayKey Holiday Holiday Date WorkDay
-
1 New Year's Day 1/01/2009 No
-
2 Australia Day 26/01/2009 No
-
3 Good Friday 10/04/2009 No
-
4 Easter Saturday 11/04/2009 No
-
5 Easter Monday 13/04/2009 No
-
6 Anzac Day 27/04/2009 No
-
7 Queen's Birthday 8/06/2009 No
-
8 Christmas Day 25/12/2009 No
-
9 Boxing Day 28/12/2009 No
-
10 Labor Day 4/05/2009 No
-
11 Ekka Show Day 3/08/2009 No
The function to analyse the table: - Function PublicHolidayCount(dtmBegin As Date, dtmEnd As Date) As Long
-
Dim db As DAO.Database
-
Dim rst As DAO.Recordset
-
Dim strSQL As String
-
Dim rv As Long
-
Set db = CurrentDb
-
strSQL = "SELECT tblPublicHolidays.HolidayDate, tblPublicHolidays.WorkDay, " & _
-
"Format([HolidayDate],'ddd') AS Day " & _
-
" FROM tblPublicHolidays " & _
-
"WHERE (((tblPublicHolidays.HolidayDate) >= #" & dtmBegin & "# " & _
-
"And (tblPublicHolidays.HolidayDate) <= #" & dtmEnd & "# ) " & _
-
"AND ((tblPublicHolidays.WorkDay)=False) " & _
-
" AND ((Format([HolidayDate],'ddd'))<>'Sat' And (Format([HolidayDate],'ddd'))<>'Sun'));"
-
-
Set rst = db.OpenRecordset(strSQL)
-
If rst.EOF And rst.BOF Then
-
rv = 0
-
Else
-
rst.MoveLast
-
rv = rst.RecordCount
-
End If
-
-
PublicHolidayCount = rv
-
End Function
The Intermediate window test - ?PublicHolidayCount(#1/2/2009#,#28/2/2009#) returns 0 which is correct (Australian date format)
-
?PublicHolidayCount(#2/1/2009#,#2/28/2009#) returns 1 which is incorrect (US date format)
The code on my form with some extra notes of explanation - Private Sub cmdDates_Click()
-
Dim intTotalWeekdays As Integer
-
Dim intHolidaysOff As Integer
-
Dim TotalWorkDays As Integer
-
Dim dtmBegin As Date
-
Dim dtmEnd As Date
-
dtmBegin = Me.txtDateBegin / retrieves dates from form
-
dtmEnd = Me.txtDateEnd
-
-
MsgBox "Start Date: " & dtmBegin /only here for testing purposes – shows dates in Australian format.
-
MsgBox "End Date: " & dtmEnd
-
-
intTotalWeekdays = TotalWeekdays(dtmBegin, dtmEnd) / passes dates to another function to calculate week days.
-
-
MsgBox "Total Weekdays: " & intTotalWeekdays / only here for testing purposes – results are accurate
-
-
-
intHolidaysOff = PublicHolidayCount(dtmBegin, dtmEnd) / THIS IS WHERE THE PROBLEM IS – it should be passing the dates in Australian format but is returning incorrect results
-
intHolidaysOff = PublicHolidayCount(Format(dtmBegin, "m/d/yy"), Format(dtmEnd, "m/d/yy")) / THIS CORRECTS THE PROBLEM – converts the date format to US format and the results are accurate.
-
-
MsgBox "Public Holidays: " & intHolidaysOff / Only here for testing purpose
-
TotalWorkDays = intTotalWeekdays - intHolidaysOff
-
-
MsgBox "Total Work Days: " & TotalWorkDays / Only here for testing purposes
-
-
End Sub
The Question
So, why does the PublicHolidayCount function work accurately with Australian dates passed from the Intermediate window but not when passed from the code attached to my form which requires the dates to be reformatted into US date format?
4 3027
Using dates in any format other than US format generally leads to problems in Access. A fellow Aussie, Allen Browne, documents this here and gives some workarounds: Microsoft Access tips: International Dates in Access
As to why it works from the Immediate Window but not from the module, I'm not sure. Access does process things somewhat differently from Immediate Window; this sort of thing pops up, from time to time. The other possibility is that the dates being processed from the Immediate Window are unequivocal dates whereas those from the module are not.
The problems caused by international date formats usually only apply to dates where there could be more than one interpretation. For example, 12/31/2008
can only be interpreted as December 31, 2008; the 31 can only represent a day, not a month. But 6/1/2008
could be June 1, 2008 or January 6, 2008. Both the 6 and the 1 could represent days or months, and hence the problem.
Linq ;0)> NeoPa 32,556
Expert Mod 16PB
Fundamentally, because dates in SQL are ALWAYS interpreted in SQL standard form, which just happens to be US format.
Access does a good job of hiding this from users as it will convert dates from local format to SQL format for you whenever IT is doing it (IE. Not when looking at the SQL directly but pretty well all other times).
To see this clearly create a simple query and add a single field (called Jan2) as "Jan2: #2 Jan#". You will see this in the grid, in your own short date format. Next use the View menu to view the SQL. You will now see this as : - SELECT #1/2/2009# AS Jan2
See Literal DateTimes and Their Delimiters (#) for a more in-depth discussion.
Thank you Linq ;0)> and NeoPa for your responses. I appreciate it.
Cheers
NeoPa 32,556
Expert Mod 16PB Sign in to post your reply or Sign up for a free account.
Similar topics
by: Hans Gruber |
last post by:
Hi all,
I have been struggling with a problem all day, I have been unable to come
up with a working solution.
I want to write a function which takes 2 unix timestamps and calculates
the...
|
by: Steven Bethard |
last post by:
I wrote:
> If you really want locals that don't contribute to arguments, I'd be
> much happier with something like a decorator, e.g.:
>
> @with_consts(i=1, deftime=time.ctime())
> def foo(x,...
|
by: clintonG |
last post by:
When the following code is run on Sat Dec 25 2004 19:54:18 GMT-0600 (Central
Standard Time)
var today = new Date();
var GMTDate = today.toGMTString();
document.write("GMTDate: " + GMTDate);
...
|
by: teddysnips |
last post by:
To set up the problem, paste this into QA:
if exists (select * from dbo.sysobjects where id =
object_id(N'.') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table .
GO
CREATE TABLE . (
...
|
by: Andy Davis |
last post by:
I have a table of data in Access 2002 which is used as the source table for
a mail merge document using Word 2002 on my clients PC. The data is
transferred OK but I've noticed that any dates which...
|
by: Jeff S |
last post by:
Not a showstopper (but annoying nevertheless):
On the Start page of VS.NET 2003, the list of projects from which I can
choose shows the Modified date of each project listed. The date actually...
|
by: insomniux |
last post by:
Hi,
I am having a problem with formatting the default value of a date
field. It has been discussed earlier in many topics, but still I cannot
solve the problem. What's happening:
I have various...
|
by: mai |
last post by:
Hi everyone,
i'm trying to exhibit FIFO anomaly(page replacement algorithm),, I
searched over 2000 random strings but i couldnt find any anomaly,, am
i I doing it right?,, Please help,,,The...
|
by: DiAvOl |
last post by:
Hello everyone,
Please take a look at the following code:
#include <stdio.h>
typedef struct person {
char name;
int age;
} Person;
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |