Disable access changing date values

[redtux]

Is there a way to stop access changing a date value which is invalid to one
taht meets any possible format

ie I enter 30/2/2006 (impossible) and access alters it to
06/06/1930

This seems to violate any data integrity principles I have ever come across

This is in Access XP

Thanks for any help

 

[Jerry Whittle]

Very strange. Even if it was evaluating 30/2/2006 as 30 divided by 2 divided
by 2006 and trying to turn in into a date, it should look something like
Saturday, December 30, 1899 12:10:46 AM.

Where are you entering this date? In a form? Has someone put some fancy code
behind the form?

What are the regional settings for the computer? English (United States) or
some other language?

 

[redtux]

Very strange. Even if it was evaluating 30/2/2006 as 30 divided by 2 divided
by 2006 and trying to turn in into a date, it should look something like
Saturday, December 30, 1899 12:10:46 AM.

Where are you entering this date? In a form? Has someone put some fancy code
behind the form?

both in the form and the base table

 

[Jerry Whittle]

Curiouser and curiouser! As it's happening at table level, it can't be any
fancy code. Here's some more stupid questions:

1. It's an Access table and not something linked to another database or
something lke Excel?

2. It's a Date/Time field?

3. You don't have a lookup or subdatasheet based on that field? No combo box
in the field?

4. Try turning off Name AutoCorrect. (I'm really grasping as straws here).
Go to Tools, Options, General Tab and turn off Name Autocorrect.

5. While the computer is set for UK and should accept DD/MM/YYY input, that
could be the problem. What happens when you try 02/30/2006?

 

[redtux]

Curiouser and curiouser! As it's happening at table level, it can't be any
fancy code. Here's some more stupid questions:

1. It's an Access table and not something linked to another database or
something lke Excel?

yep

2. It's a Date/Time field? yep

3. You don't have a lookup or subdatasheet based on that field? No combo box
in the field?
nope
4. Try turning off Name AutoCorrect. (I'm really grasping as straws here).
Go to Tools, Options, General Tab and turn off Name Autocorrect.

tried it - no effect

5. While the computer is set for UK and should accept DD/MM/YYY input, that
could be the problem. What happens when you try 02/30/2006?

these work as they should, ie throw up an error message
2/30/06
2/30/2006

bizarrely
30/2/2006

but 30/2/06 results in
06/02/1930

 

[David Cox]

but 30/2/06 results in

06/02/1930

but earlier:

was it 2006 or 06 ? or both?

could the date format be yy/mm/dd or yyyy/mm/dd ?

 

[Jerry Whittle]

I finally figured it out:

Debug.Print #30/2/06# returned 06/02/1930 as it should!

Access assumes it's YY/MM/DD as there is no 30th month so it can't be
MM/DD/YY. Next if it is the 30th day there can't be a 2nd month as that
February. Therefore Access guesses 06/02/1930. For dates under 30 and under,
Access assumes 2000's. For 30 and above, Access assumes the 1900's.

How to stop this behavior? Simply make an input mask to require a 4 year
input. As you noted, 30/2/2006 causes an error.