[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2009-02-22
Medium Priority
Last Modified: 2012-05-06

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.
Question by:usslindstrom
  • 3
  • 2
LVL 46

Expert Comment

ID: 23707773
@ 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.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23708477

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.




Author Comment

ID: 23709496
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.
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.


Author Comment

ID: 23709519
~oh - And as for properties of the field in question - is currently a Text data type with 50 maximum characters.
LVL 46

Accepted Solution

aikimark earned 500 total points
ID: 23710712
* 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.

Author Closing Comment

ID: 31549915
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.  :)

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question