Date, Dates, and how Ms access handles them (1 Viewer)

[Riverburn]

Probably this has been replied before in many topics, but I didn't really find a post informing people of this strange thing that MS access does.

on MSDN I can read that MS access will use the system date used on the host system. in my case that is dd/mm/yyyy.
However, just to be on the safe side, I explicitly tell access that my dates are formatted as follow:

Format(startDate, "DD/MM/YYYY")
//
Format(endDatum, "DD/MM/YYYY")

that will do most of the time. Access sees your dates, nods and accepts them without much ado. He's in an irritating way very pigheaded however, by changing for example 15/04/2010 into 04/15/2010 when you do a testquery.
you sigh, and you wonder why they insist, but you accept, it does what it has to do. you work work work, and then suddenly you notice that at one point that you've got all the assignments in 6 months and not those in one week.
many things can have gone wrong, arrays are not emptied, the recordset is still full, the weeknr to date does miscalculations and then you finally find it.
access doesn't see any reason to change 02/08/2010 - 08/08/2010 to 08/02/2010 - 08/08/2010 and instead gives you all the stuff between the eight of februari and the eight of august, no matter that in all the 30 other cases it's the other way around.

so, my advice is for all those poor Europeans using access, just go with mm/dd/yyyy. I won't start an argument on which date format is the most logical, though I will say that it is mighty confusing when access assures me it uses the system date while in fact it does only when it sees that there is no other logical way (i.e. 24/02/2010 cannot possible be MM/DD/YYYY)

 

[namliam]

Yes, one of access' quirks...
Save way of using dates though in any system is use ISO date: YYYY-MM-DD
Or use a 'proper' date function like DateSerial to build your dates

Neither will ever go wrong....

 

[Brianwarnock]

There is no problem with dates, Access will use your date format except in 2 clear situations, when you hardcode dates in SQL or code, then it uses US format, switching any dates that cant be valid US ie 15/04/2009 to 04/15/2009 as there cannot be more than 12 months.

Brian

 

[Riverburn]

its that last part which is a problem for me. I think I remember the MSDN specifically saying that Access handles dates the way your region handles them. since Access is mainly a Database management system, I'll end up using SQL. and in such an SQL statement some dates were turned around.

In all honestly I'd rather have him turn all dates around in American way and tell me there is an error when there are more then twelve months so that I know I can't use my system date, then just going with the American way whenever a European date can safely be misunderstood.

it did take quite a bit of searching and I didn't see it until I did a test query and he changed the dates in front of my eyes when I saved the query

 

[vbaInet]

It does get confusing. You would probably need to create your own function to disallow European date formats being entered.

 

[Riverburn]

well, luckily no dates have to be entered, only week-numbers. I decide how the dates look. a format(startDate, "MM/DD/YYYY") did the trick. And I really don't mind to use the American date format, it's American software in the end. What does frustrate me is the fact that it kinda goes along with what it has.

if it cannot be turned into an American date format, it'll be European, however, if it is, then it's changed.
Logic says that: or it listens to what my command says I.e. Format(startDate, "DD/MM/YYYY"), and only accept European date formats, or it doesn't and only accept American, but don't go switching to American format whenever my days are less then 12, it makes error control hard.

I was lucky that I noticed that 35 assignments in one week is kinda over the top, but for people entering dates and stuff, this can be a dirty bug