importing date fields from excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I try to import my date fields into an existing table in Access 2000
from Excel 2000, I get an error that tells me it is deleting the records in
that column. I can't import into a new table either. I have tried formatting
the Excel field cells in different ways (as dates, as text, as general) but
nothing seems to work. When ithe Access wizard doesn't delete the records, it
displays them as five digit numeric values and then does not let me convert
those fields in Access to the date formats I need. Please help.

Thanks,
spence
 
It's almost always best to import EXCEL data into a new table, and then use
an append query to copy the data to the permanent table. That way, you avoid
EXCEL and ACCESS disagreeing about the data types and you can control the
data's format/type as you append them.
 
Thanks, Ken. But even when I import my Excel date field into a new Access
table, Access changes to type to "text" and the resulting values display as
five digit numeric values (e.g. "38383"). Access will not allow me to change
the format of these fields back to "date." My Excel cells are formatted as
dates (3/14/1998). Then of course when I create my append query, all the
values in the date field are deleted. Is there another way I should be
formatting the Excel date field? This seems like it should be much more
straightforward that it is. I've tried it on two different computers just to
make sure my copy of Access isn't hinky.

Thanks,
Spence
 
spence said:
Thanks, Ken. But even when I import my Excel date field into a new Access
table, Access changes to type to "text" and the resulting values display as
five digit numeric values (e.g. "38383"). Access will not allow me to change
the format of these fields back to "date." My Excel cells are formatted as
dates (3/14/1998). Then of course when I create my append query, all the
values in the date field are deleted. Is there another way I should be
formatting the Excel date field? This seems like it should be much more
straightforward that it is. I've tried it on two different computers just to
make sure my copy of Access isn't hinky.

Thanks,
Spence

:
Click to expand...
I just did a test and the import wizard shows the 5 digit
number as you describe but when I finish the import it
displays in the table as a date. The field in Excel was
formatted as a date field. I made no changes to the field
in the import wizard.

gls858
 
Those five-digit numbers actually are how ACCESS stores a date. Cast them
with CDate function in the append query.

INSERT INTO Table1 ( DateTimeFieldName )
SELECT CDate([FiveDigitNumberField]) FROM Table2;

--

Ken Snell
<MS ACCESS MVP>
 
howdy....doesn't work that way for me. the five digit numeric stays. is there
a particular date format in excel you are choosing? (ken's advice looks good
but i'm in over my head with his suggestion.)
 
spence said:
howdy....doesn't work that way for me. the five digit numeric stays. is there
a particular date format in excel you are choosing? (ken's advice looks good
but i'm in over my head with his suggestion.)

:
Click to expand...
I just typed in a value 02/02/05 and it assigned a date
format automatically. *3/14/01

gls858
 
Back
Top