• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

Access 97 date function inconsistencies?

My system has been set up to use the time format dd/mm/yyyy in which the day precedes the month when displaying the date.

I have found that for some functions (such as day() and datevalue()) they would whatever format the user enters (eg. 17/5/2000 & 5/17/2000) and correctly displays the result (the day is 17th or 17/5/2000).
For other functions such as weekday(), it would not accept the system default but instead treat all date string as mm/dd/yyyy. So, if I entered weekdate(#8/1/2000#) it would return the wrong weekday because it doesn't intepret it as 8th January.

Is this a problem with Access 97 alone, or does it exist with all Office 97 products? Or is it a problem with Windows or VBA? Is there a patch for it, or other solutions? And does the same kind of problem exist with Office 2000 and/or Windows 2000?

FYI, my copy of Office 97 was bought in 1998 but I have since upgraded it to SR2. I am running Windows 98SE upgraded from an OEM (Dell) version.

Any wise and learned person out there who can shed some light on this subject - I'd be most appreciative. Thanks in advance!
0
diddler
Asked:
diddler
  • 5
  • 5
  • 3
  • +3
1 Solution
 
nico5038Commented:
The problem is well known by me. Access uses the US format as a kind of internal default. This has forced me to almost always use an explicit FORMAT statement to make sure I get the european dd/mm/yyyy I want. Even the country/regional settings aren't used by Access consequently !

Nico
0
 
nico5038Commented:
BTW: Access 2000 has the same problem and your sample 17/5/2000 & 5/17/2000 works only because there is a day larger then 12 ! just try 5/11/2000 and shiver !
0
 
paaskyCommented:
I agree with nico. Sometimes the date format is taken from regional settings, sometimes internal format is used and that's very confusing especially for us who live in Europe.

However you can use format dates using Format() function and "force" the date mask to mm/dd/yyyy that it will be handled correctly:

? CDate(Format("1/5/2000","mm/dd/yyyy"))
5.1.2000

? CDate(Format("5/1/2000","mm/dd/yyyy"))
1.5.2000

? CDate(Format("1/17/2000","mm/dd/yyyy"))
17.1.2000

(my regional setting date mask is dd.mm.yyyy)

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
almond_ukCommented:
I always define my dates as 'dd-mmm-yyyy' before any related date function call.

The results (like '12-Mar-2000') work everywhere and make the code easy to debug for someone using them later.
0
 
paaskyCommented:
almond_uk, that's smart !

Also you can format the dates to Medium or Long Date:

Format([DateField],"Medium Date")

Format([DateField],"Long Date")
0
 
SunBowCommented:
ditto all
0
 
diddlerAuthor Commented:
nico5038, paasky, almond_uk, and not least SunBow,

Thank you all for coming to help. Indeed you were all correct, and I thank you for sharing your knowledge with me. I did a somewhat thorough testing of the date/time functions until I was too tired at 5:30am (and had to get up for work) and I have come to some conclusions:

(1) That Access internally treat all date string literals as mm/dd/yyyy. Only functions like dateserial() which requires separate and ordered input of the parameters will have no hidden errors.

(2) If you use enter a date string with InputBox() or use it in an expression  in a query, Access *will* respect your locale settings!! However, in a module and debug window, it does *not*!!

For example, for those with dd/mm/yy settings, running the following in a query & the debug window will produce different results:

DateDiff("m",#5/3/2000#,#10/3/2000#)

This is the part that really stumped me because I can't see why...

Thanks again! Have a nice day!
0
 
diddlerAuthor Commented:
Adjusted points from 100 to 200
0
 
ThibaultCommented:
The way access 97 uses dates depends on the Database engine you use.

If yu use the Jet, it will use the regional parameters :
ex. (for France where we use dd/mm/yyyy)
23/05/2000 means 23th of may ....
Dim My date as date
Dim Vp_SQL as string

MyDate = now()

Vp_SQL = "SELECT tblDates.BeginDate, tblDates.RefOrder FROM tblDates WHERE tblDates.BeginDate = & #MyDate#;
.....execute Vp_SQl



If yu use MSSQL engine, it will pass the date parameters with the english syntax :
ex. (for France...)
Dim My date as date
Dim Vp_SQL as string

MyDate = now()

Vp_SQL = "SELECT tblDates.BeginDate, tblDates.RefOrder FROM tblDates WHERE tblDates.BeginDate = format(MyDate; "dd/mm/yyyy");"
.....execute Vp_SQl

Hope this will help...
0
 
nico5038Commented:
diddler,

Though you answered the question more or less by yourself:
The inconsistency of access in using the regional settings and it's internal US-format.

We saw that a month 13 in automatically interpreted as day, but the same field in the same situation is treated as mm/dd/yyyy when the day-field contains a value <= 12 !

The only certain way out is to use always a FORMAT !

So why upgrading the points?

This question/comment is giving a lot of support to people searching date-problems/solutions so deleting would be a shame, but if your satisfied, and want (like I do) save the results for use by others, then let customer support refund your points and save the question and comments !

Nico
0
 
diddlerAuthor Commented:
Nico,

I upgraded the points in thanks for your & other's feedback. I haven't got the hang of this "Grading system" yet. Previously, (and I think that's because you & others posted your thoughts as "comments"), I didn't see any way I could show my appreciation by giving you all points. But just now Thibault posted his as a "proposed answer" and I was given the chance to give him the points.
0
 
ThibaultCommented:
Thanks diddler !
0
 
paaskyCommented:
diddler,

The points are awarded to Expert when the 'suggestion' or 'answer' is accepted by Customer (You). The Experts who have helped you don't get any points if You increase points.

Unfortunately there's not yet feature that Customer could split the points between Experts if (s)he wants to give credit to all those experts who have helped him/her. However the Customer can post a request to EE Community Support area and ask CS Stuff to make the split.

The reason why we posted only comments instead of 'answer' is we have a customary here in Access Database topic area that all Experts post their suggestions as 'comment' instead of 'answer'. This prevents the question from being locked,
encouraging more robust exchanges, and avoids making the person who asked the
question reject one answer so that they can accept a different one.

I viewed Expert Thibault's profile and see he (she?) has joined EE very short time ago and is not probably aware of the customary in Access topic area so I understand why he posted an 'answer'.

I hope this clears out the point crediting system.

Best regards,
Paasky
0
 
paaskyCommented:
PS. You can always accept any comment as an answer and don't have to wait that someone posts an 'answer' (can take forever in Access topic area). There should be button on the right side of each comment for that purpose.
0
 
diddlerAuthor Commented:
paasky,

Thanks for your comments. Right now, only the buttons "Submit" (my return comment) and "Return to Access Topic Area" are available to me. But I think I am misleading you off the topic, so I'd go and study the help here later.

FYI, I have chosen to tackle the date format problem as follows:

(1) I am requesting the user to enter the date in the ISO format of YYYYMMDD. Instead of using the calendar OCX, I am now using 3 list boxes for year, month, and day. (This is like the year/month expiry date interface for the online credit card shopping pages.)

(2) I am storing the date in 3 separate integer fields. This is done so that I could (2.1) enter a partial date without Access filling in the missing part (2.2) with VBA I transfer them to a user-defined structure for more flexible handling. (Of course, it's also one byte less than the Date/Time type when I don't need the time. And for data alignment sake, I could put an extra byte in the structure and use it as an indicator for a time *before* or *after* the stored date).

Regards,

diddler
0
 
diddlerAuthor Commented:
paasky,

Thanks for your comments. Right now, only the buttons "Submit" (my return comment) and "Return to Access Topic Area" are available to me. But I think I am misleading you off the topic, so I'd go and study the help here later.

FYI, I have chosen to tackle the date format problem as follows:

(1) I am requesting the user to enter the date in the ISO format of YYYYMMDD. Instead of using the calendar OCX, I am now using 3 list boxes for year, month, and day. (This is like the year/month expiry date interface for the online credit card shopping pages.)

(2) I am storing the date in 3 separate integer fields. This is done so that I could (2.1) enter a partial date without Access filling in the missing part (2.2) with VBA I transfer them to a user-defined structure for more flexible handling. (Of course, it's also one byte less than the Date/Time type when I don't need the time. And for data alignment sake, I could put an extra byte in the structure and use it as an indicator for a time *before* or *after* the stored date).

Regards,

diddler
0
 
paaskyCommented:
Your date format tactic looks fine to me even it requires little more coding.

----

The question can be answered only once. After the answer has been accepted, you & us can only post comments.
0
 
SunBowCommented:
Hats off.

Additional thanx to diddler, for the very wonderful thing of coming back to the thread with comments after closure.

This reads best to me as a group effort, benefiting a group, some of whom have similar question, will visit thread later, and be appreciative of your additional comments.  So plz accept my thanx for that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 5
  • 5
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now