MS Access Date problem please help

[wolve]

[Sorted] MS Access Date problem please help

Hi and thanks for taking the time to read

now this is my first post and i hope i dont post in the wrong section my apologies if i do.

Now for the problem...
i have a vb6 project that manages a MS Access 2002 DB. Now one of the problems i have just come up with is the date. My application will be used by australians and our short date format is "DD/MM/YYYY".
But when i whip up a query and make the date criteria "DD/MM/YY" the access program then changes the format to "MM/DD/YYYY" automatically in the sql view of the query.And even if the record is formated or completed as "DD/MM/YYYY" it wont accept it and it errors me with garbage data.

Now i have done the following to try preventing this from happening:

1. Formated the access db to show short date format like this "DD/MM/YYYY" in the properties of the attribute.
2. Ive check my systems to see whether the "Regional & Language Option" are ok and there seems to be no issue there.
3. Reinforce the query by using variable to set the date.

but still no good ive also tried googling but i dont know how to ask google for something like this, so i came here to ask you fine people.

Any help with this is greatly appreciated



ps. if any of this does not make sense ill detail it more in the next post. but i really need help

thanks in advance

 

[wolve]

ok never mind fellas i think i have found the answer i went for ciggy and decided to give it another crack.

Ok so i have found that MS Access Dates MUST be in US Format "MM/DD/YYYY"
so there not much i can do about that

how ever if anyone wants to add anything please do

thanks anyways

 

[mgpa]

Hi, you should probably search the forum as this is a common problem with most (all?) MS products for non-Americans.

The problem stems from Access trying to obtain the international options of the PC on which the database is to be implemented upon. However, unless you have direct control of this you can get problems. I once had a UK based client whose standard build for a PC including setting the international options in US format! Hence the following ...

You will have to ensure that all input from the user is obtained in the correct format. So you must explicitly set every input format to "DD/MM/YYYY", you cannot default it, as any date where the day and month are both <= 12 will default to American format.

When displaying dates to the user, you can use the the FORMAT() command to format the dates as above. Incidentally, I tend to use "DD MMM YYYY" format (i.e. "12 Dec 2004") so that there is no mistake (you can clearly see what dates are stored.

The most dangerous problem you will encounter though is date comparisons. If you are comparing like for like you are OK (i.e. 2 dates obtained from the user and seeing how many days difference they have, it does not matter if you are comparing US, UK or Japanese formats the number of days difference will always be the same).

The problem is, what if you are interested in dates before 01/12/2004 (UK format), say. In my experience, you cannot use #01/12/2004# as this will probably be defaulted to 12 Jan 2004. My solution is not to use #01/12/2004# but to use CDate("1 Dec 2004"). This way there is no mistake in interpretation by Access.

Incidentally, if you interpret a solution performing the above, you are effectively making your application unsuitable for installation on PCs in the US!

Hope this helps.