Date Input Mask (Access 97)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Thanks for the reply

Reply | Quote

Sometimes the simplest answers are the best.

Reply | Quote