Date from Access2007 32bit backend is not correct in Access2010 64bit

[hokkienmee]

Hi,

Sorry for the somehow confusing title. I tried to be as descriptive as I already can. I have also somehow lost touch with what I did, so please forgive me if I am describing my problem the wrong way, or using the incorrect terms.

This is my problem. I wrote a database years ago for my company to use. All was well. It was running with minimum problem for a few years. It was using access 2007, with front end and back end. There are 3 front end, .accde.

I have a PC, Windows 8 64bit, Access 2010 64bit.
When I use this new PC to link to my existing access2007 back-end, the date shown are all wrong.

If the date is supposed to be 1-4-2009, it becomes 1-1-188, or something to that effect. All the dates are wrong by a thousand over years. When I use my existing access2007 to read the same back-end table, the dates showed are correct.

Any advice or hint as to where to find the answer is very much appreciated.

hokkienmee

 

[hokkienmee]

Would very much appreciate even if its a suggestion or hint. I have completely no idea.
Thanks.

 

[JHB]

Did you try to open the backend database directly in the 64 bit version?
But anyway - why are you using the 64 bit version, why not the 32 bit version instead?

 

[hokkienmee]

Hi JHB,

Thanks for the reminder.

I have opened the back-end database directly using the 64-bit version.
I have uninstalled the 64-bit office and installed the 32-bit instead.
Opened the back-end directly with 32-bit access.
I have cut-and-paste the back-end table from 32-bit access into excel.

The following is an extract of the Dates I get from various attempts.

Correct Dates: 07-Jul-09, 16-Jul-09
64-bit Access: 07-Jul-188, 16-Jul-197
32-bit Access: 07-Jul-188, 16-Jul-197
32-bit Excel: 07-Jul-09, 16-Jul-09

So far it appears that the problem is not with 64-bit/32-bit, but with access itself.
Any other suggestions is greatly appreciated.

 

[hokkienmee]

I have made further tests.
I created blank database in 32-bit access with a new table with field Date/Time, format Medium Date.

If I entered the date manually into the field, the date is shown correct.
But if I enter the date using the date picker, the date entered into table is wrong.

Date Picker, Date Field
01/04/2014, 01/04/1991
02/04/2014, 02/04/1992
03/04/2014, 03/04/1993
04/04/2014, 04/04/1994
05/04/2014, 05/04/1995
06/04/2014, 06/04/1996
07/04/2014, 07/04/1997
08/04/2014, 08/04/1998
09/04/2014, 09/04/1999
10/04/2014, 10/04/100

It seems this is appearing less of a database design issue. I have no idea what did I do wrong to get this. Should there be any reference that I should take note?

 

[Galaxiom]

What are the regional time/date settings for Windows on that machine?

 

[hokkienmee]

In the Format tab:

Short Date: d-M-y
Long Date: dddd,d MMMM, yyyy
Short Time: h:mm tt
Long Time: h:mm:ss tt
First Day of week: Sunday

In the Example, it shows:

Short date: 9-4-14
Long date: Wednesday, 9 April, 2014
Short time: 2.01 PM
Long time: 2:01:03 PM

In the Additional Settings, Date Tab,
Calendar section:
When a two-digit year is entered, interpret it as a year between:
1930 and 2029
First day of week: Sunday
Calendar type: Gregorian Calendar

 

[hokkienmee]

I am wondering if there is anyway I can rename the thread? The way it looks the problem is not what I initially thought it was.

Is there anything that I have not yet tried?

 

[hokkienmee]

Hi all,

I have finally found my whole package of installation CDs and ready to re-install every single thing, beginning with my windows 8.

Just a last attempt before re-installation, I tried to tabulate all the possible combinations that results in the mysterious error. The error is simply not showing itself anymore!

Maybe its a self aware bug that understands the consequence of re-installation.

Thanks for JHB and Galaxiom for trying to help. Much appreciated.

 

[Galaxiom]

I tried to tabulate all the possible combinations that results in the mysterious error. The error is simply not showing itself anymore!

Probably. I work in IT supporting both company staff and public members. Many times I have had the problem disappear during the phone call or just as I make a remote connection.

Once the computer realises an expert is going to look it knows there is nowhere to hide.