Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing dates (Access 2002)

    I have 2 databases - the second is a direct copy of the first. I upload *.txt files into both (after converting from *.prn via a cunning bit of code I got off microsoft's webpage).
    In the first database I have no problems importing text and dates, using an input specification naturally. In the second database there is always a conversion error on the date columns which renders them blank. If I copy and paste the *.txt file direct into the database tables window I can then amend the fields from text to date with no problems. If I look at the *.txt file in excel the columns are definitely formatted as dates.
    Obviously something is different between the 2 databases and I have searched all the options regarding international dates etc. which are all the same. I am using both databases on the same PC so my regional settings shouldn't be an issue - however someone else on the same network who has used the second database may have had different regional settings which conceivably has caused me problems.
    Any ideas about what I'm missing here?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing dates (Access 2002)

    I think this will be hard to pinpoint without seeing the databases (and probably even when seeing them) You write that the second database is a direct copy of the first one. If you make another copy of the first one, without the tables, and import the tables from the second one into it, can you then import the text files into this copy?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Importing dates (Access 2002)

    Dates will definitely cause problems if the regional settings are different because the text in the date field will be interpreted according to the current regional settings. If the same date format is not used, including the delimiters, you will get exactly the situation you describe. For that reason, we have code in our applications to always cast the dates in US format when we import and export data. Then we can use CDate() to convert the text date to the current regional settings.
    Charlotte

Posting Permissions

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