Date Issue (2003)

Topic

New Reply

I have an Excel file that I import into an Access database. In the Excel file there is a date field that looks like 12/02/08 but when it comes into Access it looks like 12/02/2008 12:35:58 PM. I can’t seem to get rid of the time. I formatted the date to date in Excel but nothing works. How can I remove the time? I can’t see it in Excel.

Reply | Quote

Replies

In Access, a data/time field is actually a number in which the date is represented by numbers to the left of the decimal and the time by numbers to the right of the decimal. You can eliminate the time by just doing an Update query and rounding the value to 0 decimal places. Like this:

UPDATE yourTable SET yourDate = round(yourDate,0)

Of course, backup your database before doing any data manipulation like this, just in case.

As for displaying dates, without any special formatting, Access uses your Windows date format as the default. This is just a formatting issue, it doesn’t affect the data.

Reply | Quote

Thank you – I will try that.

Reply | Quote

In addition to Mark’s suggestion (and always back up your tables first), you could also modify the table in design view, and change the field’s properties.

Reply | Quote