Date Format Error in Access

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

Guest

I am trying to import a text file with date fields in a format such as:

2006-03-21 21:10:15.253 (YYYY-MM-DD HH:MM:SS.SSS)

Excel recognizes the date format, but Access will not. Any suggestions?
 
well, i take it that SSS is fractions of a second? AFAIK, Access can't store
a time value smaller than a second. to simply get the data into a table
without losing it, you could probably save the fraction into a separate
field in the record - at least it's be available to further study how you
might utilize it.

as for the rest of the date/time value
2006-03-21 21:10:15
you can import the value intact, and correctly, and easily, into a single
Date/Time field. from the database window, on the menu bar click File | Get
External Data | Import. choose the text file in the dialog. when the Import
Text Wizard opens, click the Advanced button in the lower left corner. in
the Import Specification dialog, go to the Dates, Times, and Numbers section
in the center and set the options to match your text data's format. if
you're going to import this data more than once, Save the import
specification for future use, so you don't have to keep rebuilding it. then
click OK.

hth
 
Hi Dan,

Yes date fields always a problem.

Few Suggestions are .

1) Import as a text field, (Text Fields stops import errors) if it comes
throught as a interger then it is already a date field.

if Not

Then after import you can use Left or Mid Or Right or any combination on
this theme to obtain your date field.

Hope this helps.

If not and need more just ask.

Regards

Trev
 
Back
Top