Cut/Paste date in Access turns into decimal / Need Query to convert it back
Posted on 2009-02-22
This is probably a relatively easy problem - but I'm stumped.
I have an access database that I frequently cut / paste from different locations (such as an internal website for the enterprise). Unfortunately when I cut and paste from the site - the date field gets converted into a decimal number inside any MS Office program.
For example: I cut / paste this date from the internal site "2/22/2009 11:59:37 PM" and MS Access converts it to "39866.9997337963". Of course if I do the same thing in Excel, it gives me the same number - however with Excel, I can just convert the cell into a "Date" block, and it automatically changes it back to a readable format.
Unfortunately - with Access, I can't seem to do this. If I change the column type to a date field - it's unable to procede. For this reason, I've had to leave the column a text field.
If possible, I need to build a query that can convert that text block - back into a readable date / time. I've already been working with the "FORMAT" statement, but haven't had any luck.
Any ideas that can be thrown into the mix would be greatly appreciated.