date is automatically changed

[velskvm]

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

[comteck]

WHAT???

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

comteck

[gpmartino]

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

[MMcCarthy]

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

[nico5038]

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)

[NeoPa]

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 :).

[MMcCarthy]

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

[NeoPa]

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 ;).

[MMcCarthy]

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

[NeoPa]

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.

[MMcCarthy]

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