Access 2007: Support for formatting long integers as date broken?

Ever since Access 2.0, i have been using fields of type long integer
as a subsitute for date fields, since I don't need the time part most
of the time. Setting the fields format property (or the format
property of a bound textbox) to "short date" not only showed the
stored number as a date, it was also used as an input mask: you could
only enter values that corresponded to valid dates as indicated by the
local windows settings. This was comparable to formatting Excel cells
as "date".

In Access 2007, this support is halfway gone. It still works for
unbound text boxes (even displaying the date picker), but with text
boxes bound to a database field of type number or in a table view,
only the display part is correct. Data newly inputted is interpreted
as a plain number. Since the german date delimiter is the same as the
thousands separator, it took some time to figure out what is really
happening. For example, a date entered as "29.07.08" is interpreted as
ISO Date 2008-07-29 in Access 97-2003, but as the number 290708 in
Access 2007, which is then converted to ISO date 2695-05-12.

I could not find this change in behaviour documented anywhere. Am I
the only one experiencing this problem, does anybody know a solution
to this problem? Can it be solved at the form level? I really don't
want to change the type of hundreds of database fields, since this
would lead to a massive size increase and also pose integrity problems
as I wold have to make sure the time part is always "zeroed out"
before saving.

Thanks in advance
Reimund

Well, I've never done that (storing date/time data in a Long Integer type),
but I can confirm that it doesn't work any more in Access 2007, as it did in
A2003.

Have added it to the end of the compatibility issues he
http://allenbrowne.com/Access2007.html#Compatibility

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Reimund" wrote in message
...
Ever since Access 2.0, i have been using fields of type long integer
as a subsitute for date fields, since I don't need the time part most
of the time. Setting the fields format property (or the format
property of a bound textbox) to "short date" not only showed the
stored number as a date, it was also used as an input mask: you could
only enter values that corresponded to valid dates as indicated by the
local windows settings. This was comparable to formatting Excel cells
as "date".

In Access 2007, this support is halfway gone. It still works for
unbound text boxes (even displaying the date picker), but with text
boxes bound to a database field of type number or in a table view,
only the display part is correct. Data newly inputted is interpreted
as a plain number. Since the german date delimiter is the same as the
thousands separator, it took some time to figure out what is really
happening. For example, a date entered as "29.07.08" is interpreted as
ISO Date 2008-07-29 in Access 97-2003, but as the number 290708 in
Access 2007, which is then converted to ISO date 2695-05-12.

I could not find this change in behaviour documented anywhere. Am I
the only one experiencing this problem, does anybody know a solution
to this problem? Can it be solved at the form level? I really don't
want to change the type of hundreds of database fields, since this
would lead to a massive size increase and also pose integrity problems
as I wold have to make sure the time part is always "zeroed out"
before saving.

Thanks in advance
Reimund

Allen,

How would you ferret out compatibility issues after converting an AccessXP
database to Access2007? Do you think the Access2007 database would compile
if there was a compatility issue?

Steve


"Allen Browne" wrote in message
...
Well, I've never done that (storing date/time data in a Long Integer
type), but I can confirm that it doesn't work any more in Access 2007, as
it did in A2003.

Have added it to the end of the compatibility issues he
http://allenbrowne.com/Access2007.html#Compatibility

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Reimund" wrote in message
...
Ever since Access 2.0, i have been using fields of type long integer
as a subsitute for date fields, since I don't need the time part most
of the time. Setting the fields format property (or the format
property of a bound textbox) to "short date" not only showed the
stored number as a date, it was also used as an input mask: you could
only enter values that corresponded to valid dates as indicated by the
local windows settings. This was comparable to formatting Excel cells
as "date".

In Access 2007, this support is halfway gone. It still works for
unbound text boxes (even displaying the date picker), but with text
boxes bound to a database field of type number or in a table view,
only the display part is correct. Data newly inputted is interpreted
as a plain number. Since the german date delimiter is the same as the
thousands separator, it took some time to figure out what is really
happening. For example, a date entered as "29.07.08" is interpreted as
ISO Date 2008-07-29 in Access 97-2003, but as the number 290708 in
Access 2007, which is then converted to ISO date 2695-05-12.

I could not find this change in behaviour documented anywhere. Am I
the only one experiencing this problem, does anybody know a solution
to this problem? Can it be solved at the form level? I really don't
want to change the type of hundreds of database fields, since this
would lead to a massive size increase and also pose integrity problems
as I wold have to make sure the time part is always "zeroed out"
before saving.

Thanks in advance
Reimund

"Steve" wrote in message
m...

How would you ferret out compatibility issues after converting an AccessXP
database to Access2007? Do you think the Access2007 database would
compile if there was a compatility issue?

Not sure I understand the question.

There are cases where A2007 would compile, even though there is a
compatibility issue between the 2 versions. This thread (date stored as long
integer) is an example.

There are other cases where the A2007 database would not compile, but there
is no compatibility issue between the 2 versions. For example, a compact,
decompile, compact could fix that kind of problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thank you for responding!!

If you convert an Access XP database to Access 2007, do you do anything more
than see if the new database compiles? If it compliles do you just put the
database into production and see if any errors occur?

Steve


"Allen Browne" wrote in message
...
"Steve" wrote in message
m...

How would you ferret out compatibility issues after converting an
AccessXP database to Access2007? Do you think the Access2007 database
would
compile if there was a compatility issue?

Not sure I understand the question.

There are cases where A2007 would compile, even though there is a
compatibility issue between the 2 versions. This thread (date stored as
long integer) is an example.

There are other cases where the A2007 database would not compile, but
there is no compatibility issue between the 2 versions. For example, a
compact, decompile, compact could fix that kind of problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steve" wrote in message
news

If you convert an Access XP database to Access 2007, do you do anything
more than see if the new database compiles? If it compliles do you just
put
the database into production and see if any errors occur?

Okay, it would be good to be aware of a range of other issues that might
need your attention as well. Here's a list:
http://allenbrowne.com/Access2007.html

If you don't have A2007, but have a database that may be used by A2007
users, you may be interested in this:
http://allenbrowne.com/ser-69.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.