473,378 Members | 1,434 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

Date Function anomaly

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:
Expand|Select|Wrap|Line Numbers
  1. HolidayKey Holiday Holiday Date WorkDay
  2. 1 New Year's Day 1/01/2009 No
  3. 2 Australia Day 26/01/2009 No
  4. 3 Good Friday 10/04/2009 No
  5. 4 Easter Saturday 11/04/2009 No
  6. 5 Easter Monday 13/04/2009 No
  7. 6 Anzac Day 27/04/2009 No
  8. 7 Queen's Birthday 8/06/2009 No
  9. 8 Christmas Day 25/12/2009 No
  10. 9 Boxing Day 28/12/2009 No
  11. 10 Labor Day 4/05/2009 No
  12. 11 Ekka Show Day 3/08/2009 No
The function to analyse the table:
Expand|Select|Wrap|Line Numbers
  1. Function PublicHolidayCount(dtmBegin As Date, dtmEnd As Date) As Long
  2. Dim db As DAO.Database
  3. Dim rst As DAO.Recordset
  4. Dim strSQL As String
  5. Dim rv As Long
  6. Set db = CurrentDb
  7. strSQL = "SELECT tblPublicHolidays.HolidayDate, tblPublicHolidays.WorkDay, " & _
  8. "Format([HolidayDate],'ddd') AS Day " & _
  9. " FROM tblPublicHolidays " & _
  10. "WHERE (((tblPublicHolidays.HolidayDate) >= #" & dtmBegin & "# " & _
  11. "And (tblPublicHolidays.HolidayDate) <= #" & dtmEnd & "# ) " & _
  12. "AND ((tblPublicHolidays.WorkDay)=False) " & _
  13. " AND ((Format([HolidayDate],'ddd'))<>'Sat' And (Format([HolidayDate],'ddd'))<>'Sun'));"
  15. Set rst = db.OpenRecordset(strSQL)
  16. If rst.EOF And rst.BOF Then
  17. rv = 0
  18. Else
  19. rst.MoveLast
  20. rv = rst.RecordCount
  21. End If
  23. PublicHolidayCount = rv
  24. End Function
The Intermediate window test
Expand|Select|Wrap|Line Numbers
  1. ?PublicHolidayCount(#1/2/2009#,#28/2/2009#) returns 0 which is correct (Australian date format)
  2. ?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
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDates_Click()
  2. Dim intTotalWeekdays As Integer
  3. Dim intHolidaysOff As Integer
  4. Dim TotalWorkDays As Integer
  5. Dim dtmBegin As Date
  6. Dim dtmEnd As Date
  7. dtmBegin = Me.txtDateBegin / retrieves dates from form
  8. dtmEnd = Me.txtDateEnd
  10. MsgBox "Start Date: " & dtmBegin /only here for testing purposes – shows dates in Australian format.
  11. MsgBox "End Date: " & dtmEnd
  13. intTotalWeekdays = TotalWeekdays(dtmBegin, dtmEnd) / passes dates to another function to calculate week days.
  15. MsgBox "Total Weekdays: " & intTotalWeekdays / only here for testing purposes – results are accurate
  18. intHolidaysOff = PublicHolidayCount(dtmBegin, dtmEnd) / THIS IS WHERE THE PROBLEM IS – it should be passing the dates in Australian format but is returning incorrect results
  19. 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.
  21. MsgBox "Public Holidays: " & intHolidaysOff / Only here for testing purpose
  22. TotalWorkDays = intTotalWeekdays - intHolidaysOff
  24. MsgBox "Total Work Days: " & TotalWorkDays / Only here for testing purposes
  26. 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?
Jan 23 '09 #1
4 3029
3,532 Expert 2GB
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,


can only be interpreted as December 31, 2008; the 31 can only represent a day, not a month. But


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)>
Jan 23 '09 #2
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT #1/2/2009# AS Jan2
See Literal DateTimes and Their Delimiters (#) for a more in-depth discussion.
Jan 23 '09 #3
Thank you Linq ;0)> and NeoPa for your responses. I appreciate it.

Jan 23 '09 #4
32,556 Expert Mod 16PB
You're welcome :)
Jan 23 '09 #5

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: 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: 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...
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.