more on screwy dates

[Janet Russell]

The Data Type IS set to Date/Time, but some entries are
coming into Excel as dates and some are coming in as
text. There must be a way to convert all data in the
table to be actual dates, either by copying the data into
a new table, an update query, or something like that.
Does anyone have any ideas?

answer received:

The critical thing you should be looking at is not the
input mask, but the
Data Type. If the Data Type is Date/Time, then it should
go to Excel with
no problems, regardless of the input mask or format
specified in Access.

HTH,

Marshall Smith
Project Developers, Inc.

Original question:

I have a database that we've been using for several
years. There are several date fields in the employee
table - birth date, start date, leave date, etc. The
input mask in the table for these dates is 99/99/9999;0;_,
although it might have been different when the database
was first created.

When I copy the data from this table to Excel, some of the
dates arrive looking like a date, but apparently are
really text, while others come in as the serial number. I
can convert those "text dates" to the serial date with the
datevalue() function in Excel, and that tells me they are
actually text.

I have created a new table with the input mask
99/99/0000;0;_, for those dates, then tried to paste
append the data from the old table into it. I thought this
might force all dates to now be in the correct format.
Everything seems to work just fine during the paste
append, but once again, when I copy the data into Excel,
some are text and some are the serial date number.

How can I get them all to be dates instead of a mix of
dates and text?

..

 

[John Vinson]

The Data Type IS set to Date/Time, but some entries are
coming into Excel as dates and some are coming in as
text. There must be a way to convert all data in the
table to be actual dates, either by copying the data into
a new table, an update query, or something like that.
Does anyone have any ideas?

If the datatype is Date/Time, they ARE dates - regardless of the
Format or the Mask, a Date/Time is stores as a double float number, a
count of days and fractions of a day since midnight, December 30,
1899. Why they're not coming over to Excel correctly I have no idea,
but you might try creating a calculated field in a Query: either

DateValue([datefield])

which will trim off any time portion, or

Format([datefield], "mm/dd/yyyy")

to get a text string which Excel will recognize as a date.