Import numeric field/need to convert to date field

[dmeid]

I am importing a file through ODBC. One of the fields is a numeric field (which is a date). When I bring it into access is comes in as a numeric field, but I need to have it as a Date/Time field for query purposes. When I try to copy/append to a new table which I have the numeric field copy into a date format...it comes up with an error and this field will not copy. Any idea on how I can change this field to a Date/Time field and retain all by data?

 

[The_Doc_Man]

It depends on a few factors but there may be some hope.

Import the "date" field as a number. Determine from the vendor's manuals or from the DBA for your ODBC partner system what the base date is for the date scheme.

For example, OpenVMS dates are the number of days since Nov. 17, 1868 (or something bizarre like that). UNIX starts from Jan. 1, 1970. Access supposedly starts from Jan. 1, 1900

OK, now use the DATEADD function to add the number to the base date. The result of DATEADD is a date. So instead of copying the table, do a MAKETABLE query that copies each field EXCEPT the date field. For that one, use the function instead.

ActualDate = DateAdd( "d", number, base-date)

 

[dmeid]

I tried the function when I created a MAKETABLE Query....but I received a 'Type conversion Error'. The table was created with the Date/Time field for the numeric field, but it did not bring over the data. The error said the (date type) field doesn't match the destination field. I also tried the 'DateValue' function but I came away with the same results.

Any Help would be greatly appreciated. Thanks dmeid