Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Oct 2001
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Coverting Access 97 to Access 2000 Date Prob. (Access 97 to 2000)

    We've just installed Access 2000 on a computer with Win 98 and we have old Databases I loaded on from Access 97. We got them converted all right but every field with a 2002 date now reads 1902. I.E. 1/15/2002 reads 1/15/1902. This is a not that old Dell Computer, 300MzHz. Is there a way to change all the 1902's to 2002 easily and what do you think happened? Understandably it's driving our coordinator who has to keep up with orders,sales etc. bananas!
    Thanks in advance.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Coverting Access 97 to Access 2000 Date Prob. (Access 97 to 2000)

    If your dates in Access 97 really were 2002 dates, then something very strange has happened. Two possibilities come to mind in terms of how it might have happened. One is that the computer at some point reset it's internal clock to 1902 and you were capturing the system date in records, but only displaying two digits of the year instead of 4. The other would have been people putting in dates by simply typing two digits instead of 4, and the system interpreting them as 1900 dates instead of 2000 dates. Service Release 2 of Office 97 corrected some Y2K problems, and this Microsoft Knowledge Base article seems to pretty much describe your situation. The next step is to find out whether Service Release 2 had been installed.

    As to fixing the problem, you could run a query to update all the records. One solution would be to update the field using the DateSerial function. Another would be to use the DateAdd function and add 100 years to everything. But first you should probably see if there are any dates that are 1901 instead of 2001 or 1900 instead of 2000. Hope this helps.
    Wendell

  3. #3
    Lounger
    Join Date
    Oct 2001
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coverting Access 97 to Access 2000 Date Prob. (Access 97 to 2000)

    I was not precise in my information. Access 2000 is not only moving new entries up to the middle of the table but it also begins numbering down from 385 million! i.e., -385,999,999 for example then -385,999,998. Totally bizarre! We were able to get the entries in order by sorting by entry date but the numbering is still completely whacko. Nothing we can see or have tried changes it. If the field is shortened the computer reduces it to mathematical notation. When we first converted to 2000 fom Access97 there were no problems. She just downloaded an Access patch from the Microsoft website but it didn't affect the numbering. Any clues?

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Coverting Access 97 to Access 2000 Date Prob. (Access 97 to 2000)

    It sounds like somehow in the updating process the AutoNumber field got changed from Increment to Random. That option assigns any new record a number between approximately -2,000,000,000 and +2,000,000,000 assuming there isn't such a number already. (If that breaks funny, it's a minus 2 billion. When you've sorted out exactly what's happening, you may want to rebuild the primary key just to keep records in synch, though it isn't really necessary. It sounds as if you may be working at the table level when doing data entry - is that so?
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •