Date formats

[Guest]

The text file I am importing has 38230 as 8/31/04. I Can't get acess to
convert 38230 to 8/31/04. Tried to use a append query and format those
fields to "date/time" but that delets the dates. Yet if I import to a excel
worksheet with the fields formated to "date" the date is converted. But using
excel causes problems by having to keep manually saving as a worksheet
instead of the text de-limited format the data is imported as.

 

[Douglas J. Steele]

Actually, 38230 IS 31 Aug, 2004 as far as Access is concerned. Access stores
dates as 8 byte floating point numbers, where the integer part represents
the date as the number of days relative to 30 Dec, 1899 (and the decimal
part represents the time as a fraction of a day).

Go to the Immediate window (Ctrl-G), type Format(38230, "m/d/yy") and hit
Enter.

However, you may need to import them as numbers, then add a new Date field
to your table and use an Update query to transfer the values from the
numeric field to the date field.

UPDATE MyTable SET MyDateField = CDate(Format([MyNumericField],
"mm/dd/yyyy"))