Linking tables with Dates from text files

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

Guest

I have a text file from our system that stores dates as text. The dates are in the form of a number from 00000 to 37000 (I believe this is days since 1900). I want to link this table so it can be queried, used in reports, etc. I prefer not to import it each time and then change the data field type each time. Is there a way to accomplish this through the advanced button in the link wizard so that the dates show up as mm/dd/yyyy?

Thanks!
 
Hi Keith

No, there is no way to have Access automatically interpret such a numeric
field as a date.

However, if you can just treat it as what it is - a number - it is very easy
to set up a query with a calculated field:
MyDate: DateAdd( "d", [Numeric Field], #1/1/1900# )

This will just add the number of days to the base date you specify and give
the result as a date.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

KeithZ said:
I have a text file from our system that stores dates as text. The dates
Click to expand...
are in the form of a number from 00000 to 37000 (I believe this is days
since 1900). I want to link this table so it can be queried, used in
reports, etc. I prefer not to import it each time and then change the data
field type each time. Is there a way to accomplish this through the advanced
button in the link wizard so that the dates show up as mm/dd/yyyy?
 
Back
Top