Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    MN
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date format issue

    I am using Access 2000 and linking to a text delimited table. When I create the link, there are several date fields for which I select the date/time format. The date/time format is the format suggested so all I do is click on "next" to move to the next field. The dates are stored in the db in the format "20-FEB-01" (without the quotes.) After finishing the link, if I open the table the columns with the dates in them all show "#Num". What am I doing wrong here? Thanks in advance!

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date format issue

    >The date time format is the format suggested

    I would suggest that your date/time format in regional settings may not match the date time format in the source. Also there is a setting in Tools/Options/Digital to use four digit year formatting which would override the regional settings.
    David Grugeon
    Brisbane Australia

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

    Re: Date format issue

    If your table is a text file, it doesn't contain dates per se, only strings and numbers, even if they represent dates. Is the value in the "date" field stored as a string with quotes around it or a string without quotes, or a number? I know you said "no quotes", but if you open the text file with a text editor, what do you see? If it isn't in a format Access can interpret as a date, you can't use a date/time format for that field. Your best bet might be to call it a string and then try using a date conversion to do whatever you're trying to do with the date.
    Charlotte

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    MN
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format issue

    The actual text string is 21-FEB-01 without the any quotes or anything else attached. Shouldn't that be a recognized date format? I just tried importing the table and selecting these fields as text. I then could go back and convert them to date format. That works. Is there any way to do this with a link so I don't have to go through this process everything I use the table? Thanks!

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

    Re: Date format issue

    I'm not sure what you mean by doing this with a link. If you build an import spec based on one of your linked tables and specifying text fields for your date fields, you should be able to use that spec to link any text file with the same structure.

    You could always build a query based on your textfile, put a calculated date field into the query based on your "date" field, and link to another table or query on the calculated field, if that's what you're trying to accomplish.

    The problem with the existing format seems to be that Access tries to compute a value for the field. If you type 21-FEB-01 into the immediate window, you'll see Access return a 20! But if you type in #21-Feb-01#, it will return 2/21/01.
    Charlotte

  6. #6
    Lounger
    Join Date
    Jan 2001
    Location
    MN
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format issue

    You nailed the problem in the last paragraph. Now that I understand what is going, I think I can deal with it. Thanks!

Posting Permissions

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