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

Experts,

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.
LVL 5
usslindstromAsked:
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:
   CDate()
   CDate(CDbl())
   CDate(CLng())
You can use these functions in an update query to convert non Null values.
0
 
aikimarkCommented:
@ 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.

0
 
Jeffrey CoachmanMIS LiasonCommented:
usslindstrom,

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.
;-)

Thanks,

JeffCoachman

0
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.
0
 
usslindstromAuthor Commented:
~oh - And as for properties of the field in question - is currently a Text data type with 50 maximum characters.
0
 
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.  :)
0
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.