Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Input Mask (Access 97)

    I am trying to import a txt file for which I can not get the date correct. The import file uses the format DDMMMYYY i.e. 29FEB2000. How do I set up the input mask for this date format so Access recognizes it as a date? I've been messing with this for a while and can't seem to get it right. Thanks

  2. #2
    Star Lounger
    Join Date
    Feb 2001
    Location
    Wirral, Merseyside, Merseyside, England
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Input Mask (Access 97)

    I've had this problem a lot of times. Access is very happy with the dd/mm/yy or mm/dd/yy format but often has difficulties with other formats.

    Easiest way to save too much playing about is to set the date field in the table the data is to be imported to a text field and save this as another table.

    Import the data into the new table, then run a query to append from the new table to the original but which formats the date as Expr1: Left([field],2) & "/" & Mid([field],3,3) & "/" & Right([field],2)

    Its a bit of a fudge but it works.

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Input Mask (Access 97)

    Eeek. I'll try to figure it out (just starting with Access). I wonder why Excel handles it and Access doesn't. Does Access 2000 have the same problem?

    Thanks for your reply.

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

    Re: Date Input Mask (Access 97)

    Excel translates anything that looks like a date into a date serial number, which is how it stores them under the hood. Have you checked to see if the date it comes up with is the correct one?

    Access handles datatypes based on what you tell it they are and based on the system settings for date format. If your system date setting doesn't match the format, then Access can't resolve the value.
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Input Mask (Access 97)

    I didn't really check the Excel dates, good catch.

    I resolved my problem the easy way. I went to the person who generates the file I'm importing and asked if the date format could be changed to mm/dd/yyyyy. No problem. Wish I had thought of that before I spent a bunch of time trying to make it work. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thanks for the reply

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

    Re: Date Input Mask (Access 97)

    Sometimes the simplest answers are the best. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    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
  •