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

Posted on 2009-02-22
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
    LVL 44

    Expert Comment

    @ 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

    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.



    LVL 5

    Author Comment

    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.
    LVL 5

    Author Comment

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

    Accepted Solution

    * 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.
    LVL 5

    Author Closing Comment

    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

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now