Cut/Paste date in Access turns into decimal / Need Query to convert it back


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.
Who is Participating?
aikimarkConnect With a Mentor Commented:
* the message indicates that one or more columns will be set to Null where they can't be converted to the new data type.

* run a query on that test column and add a column to your query:
IsDate([name of text column])

* examine the results to see what is already in your table that IS NOT considered a date

* there are a variety of methods to convert data to a date type.  Consider:
You can use these functions in an update query to convert non Null values.
@ usslindstrom

Where are doing your pasting in the MS-Access application?  Dates are stored internally as a double floating point value.  The number you posted is equivalent to the date you posted.  If you paste the text into a date field, what happens?

You might experience a problem if your system is in a d/m/yyyy region and the text date is m/d/yyyy.

Jeffrey CoachmanMIS LiasonCommented:

First, can you explain exactly what happens when you say Access is "unable to procede"?
I have never had an issue with changing the datatype (or the format) to a date.

In other words, tell us exactly what the properties of the Filed in the Table are currently, and what the properties of the textbox in the form are set to.



Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

usslindstromAuthor Commented:
No problem.   - Thanks for the help guys.  It's much appreciated.

aikimark: - I'm using a form to paste directly into a working table in my access DB.  Pretty much, the cut paste, is to track a bunch of information that's uploaded to an internal webserver.  As I don't have direct access to the back end of the webserver, but need to use the data and even store it for historical purposes, the Access idea surfaced.

The date/time as stored on the website is MM/DD/YYYY  HH:MM:SS     But even when I try to use that format for Access, it doesn't seem to like it.

And to answer Jeff - the exact error I'm getting when I try to change the text block to a date field - no matter how I try and play with the format - is this:  "Microsoft Office Access encountered errors while converting the data.  The contents of fields in xx record(s) were deleted.  Do you want to proceed anyway?  Yes/No"  - and I gather that it's just saying that since I won't meet the criteria of the new format (date/time) that it's gonna' have to dump the records.
usslindstromAuthor Commented:
~oh - And as for properties of the field in question - is currently a Text data type with 50 maximum characters.
usslindstromAuthor Commented:
Thank you very much for this solution - it worked like a champ.

I wasn't successful with the IsDate function - and kept getting errors no matter how I parsed it.  The CDate() function converted the numbers flawlessly though.

Thank you very much.  :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.