473,326 Members | 2,438 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,326 software developers and data experts.

Date Function anomaly

31
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'));"
  14.  
  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
  22.  
  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
  9.  
  10. MsgBox "Start Date: " & dtmBegin /only here for testing purposes – shows dates in Australian format.
  11. MsgBox "End Date: " & dtmEnd
  12.  
  13. intTotalWeekdays = TotalWeekdays(dtmBegin, dtmEnd) / passes dates to another function to calculate week days.
  14.  
  15. MsgBox "Total Weekdays: " & intTotalWeekdays / only here for testing purposes – results are accurate
  16.  
  17.  
  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.
  20.  
  21. MsgBox "Public Holidays: " & intHolidaysOff / Only here for testing purpose
  22. TotalWorkDays = intTotalWeekdays - intHolidaysOff
  23.  
  24. MsgBox "Total Work Days: " & TotalWorkDays / Only here for testing purposes
  25.  
  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 3027
missinglinq
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,

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)>
Jan 23 '09 #2
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 :
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
OzNet
31
Thank you Linq ;0)> and NeoPa for your responses. I appreciate it.

Cheers
Jan 23 '09 #4
NeoPa
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

4
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...
6
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,...
17
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); ...
2
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 . ( ...
7
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...
3
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...
9
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...
1
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...
160
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;
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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...
0
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...
1
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)...
1
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...
1
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....
0
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
0
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 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.