Solved

Access 97 date function inconsistencies?

Posted on 2000-05-17
18
257 Views
Last Modified: 2012-08-14
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
Comment
Question by:diddler
  • 5
  • 5
  • 3
  • +3
18 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 2816550
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
 
LVL 54

Expert Comment

by:nico5038
ID: 2816557
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
 
LVL 10

Expert Comment

by:paasky
ID: 2816600
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
 
LVL 1

Expert Comment

by:almond_uk
ID: 2816990
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
 
LVL 10

Expert Comment

by:paasky
ID: 2817144
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
 
LVL 24

Expert Comment

by:SunBow
ID: 2819543
ditto all
0
 

Author Comment

by:diddler
ID: 2819946
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
 

Author Comment

by:diddler
ID: 2819947
Adjusted points from 100 to 200
0
 
LVL 1

Accepted Solution

by:
Thibault earned 200 total points
ID: 2836976
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 54

Expert Comment

by:nico5038
ID: 2837660
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
 

Author Comment

by:diddler
ID: 2840003
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
 
LVL 1

Expert Comment

by:Thibault
ID: 2844885
Thanks diddler !
0
 
LVL 10

Expert Comment

by:paasky
ID: 2844976
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
 
LVL 10

Expert Comment

by:paasky
ID: 2844980
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
 

Author Comment

by:diddler
ID: 2852672
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
 

Author Comment

by:diddler
ID: 2852674
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
 
LVL 10

Expert Comment

by:paasky
ID: 2853082
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
 
LVL 24

Expert Comment

by:SunBow
ID: 2878103
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now