By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 2,185 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

date is automatically changed

P: 1
hello experts'

when the date is after the 12th of the month it is all working fine. It
only reverts to US format if the date is before the 12th.

Give solutions
Thank you
Jul 25 '06 #1
Share this Question
Share on Google+
10 Replies


100+
P: 179
WHAT???

How about a little more detail explaining what your problem is.

comteck
Jul 25 '06 #2

P: 1
Hi, I think I know what you are talking about and I am having the same problem, but donīt have a solution.
When working on a Spanish version of Access 2002 , I create a querydef where one of the fields is a date with criterias specified in a textbox in the format dd/mm/yyyy. When Access created the query, I open it to find that the date in the criteria had the month and the day switched automatically, but only for the dates where the day < 12......
no idea how to solve it
Dec 6 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi, I think I know what you are talking about and I am having the same problem, but donīt have a solution.
When working on a Spanish version of Access 2002 , I create a querydef where one of the fields is a date with criterias specified in a textbox in the format dd/mm/yyyy. When Access created the query, I open it to find that the date in the criteria had the month and the day switched automatically, but only for the dates where the day < 12......
no idea how to solve it
I've noticed this problem myself on some databases. I usually resolve it by imposing a date format on the field in the table as it only seem to happen with controls. With an unbound control I find that imposing a format seems to work.

I don't know why it happens in some databases and not in others. I can only assume that the designer had their system settings on US format and the engine somehow remembers this. Maybe some of our other experts will have a better idea.

Mary
Dec 7 '06 #4

nico5038
Expert 2.5K+
P: 3,072
Guess the problem that has costed me most of my debugging time.
In a query Access will use the US format. In such a case a date reads like:
mm/dd/yyyy
When there's a "mm" encountered > 12 Access will "know" that it's no real month but a day and swap them. Regrettably only those date fields will be swapped (others will remain unchanged) and as there's no further warning Microsoft is here corrupting the data.
The 100% solution wuld be to work only with US dates in a system where the Regional settings (From the Settings>Control panel) also use mm/dd/yyyy.

Personally I force entered dates in the format yyyy/mm/dd that will translate always correctly in a query. For other dates on forms, reports, etc. I don't use formatting as I leave it to the regional settings how dates are displayed.

Nic;o)
Dec 7 '06 #5

NeoPa
Expert Mod 15k+
P: 31,186
The ANSI standards for SQL specify that dates should specifically be entered in m/d/y format.
This is completely independent of regional settings and is the reason I keep trying to get Mary to use the Format() function when posting answers that include dates (as well as the # delimiters).
Nico is absolutely right about the program interpreting any strings correctly when it is able to work it out without any ambiguity. I guess that a y/m/d format is also unambiguous in that not even Americans ;) would try to use y/d/m format (Although since 2000 it is necessary to use yyyy/m/d as yy can be misinterpreted as days or months for another 25 and 5 years respectively).
Personally I believe something like y/m/d would have been far more logical (it even sorts properly if required) but the fools never thought to ask me :).
Dec 8 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Ok, Ade,

If this resolves the debugging issues I've had as Nico mentioned I will pay more attention.

Nico,

I hadn't thought of that as a solution. Sounds like a great idea.

Mary
Dec 8 '06 #7

NeoPa
Expert Mod 15k+
P: 31,186
Ok, Ade,

If this resolves the debugging issues I've had as Nico mentioned I will pay more attention.

Nico,

I hadn't thought of that as a solution. Sounds like a great idea.

Mary
Hi Mary :)
Thought you weren't on tonight.
I wouldn't recommend Nico's solution as a primary solution (please revisit updated post above re y/m/d format) as, even if it works, it is non-standard. It should work in almost all cases but if you're having to change, you may as well change to the 'standard' way.

I'm sorry Mary.
Clearly I hadn't explained this to you before - I thought I had and was confused that you didn't 'get' it. A 'senior moment' on my part obviously ;).
Dec 8 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
I was at a xmas do. Just got home.

Sorry my programming was so slow to update the data you were trying to impart. :)

Have to speak to KUB about my interpretation procedures.

Mary
Dec 8 '06 #9

NeoPa
Expert Mod 15k+
P: 31,186
I was at a xmas do. Just got home.

Sorry my programming was so slow to update the data you were trying to impart. :)

Have to speak to KUB about my interpretation procedures.

Mary
Yes, we'll get KUB to give you a small tweak.
That may put a smile on your face ;).

It seems however, that if I never explained it to you in the first place, then my confusion was misplaced.
So you're routines are still working fine :).

Hope all was fun at the party.
Dec 8 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Yes, we'll get KUB to give you a small tweak.
That may put a smile on your face ;).

It seems however, that if I never explained it to you in the first place, then my confusion was misplaced.
So you're routines are still working fine :).

Hope all was fun at the party.
Good piss up.

Lots of techie talk and plenty of gossip.

My idea of a good time.

Mary
Dec 8 '06 #11

Post your reply

Sign in to post your reply or Sign up for a free account.