Results 1 to 8 of 8
  1. #1
    LovellDC is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Feb 2017
    Posts
    12

    Change short text to date/time format in an existing table with dates in it without deleting records

    Access 2013

    Does anyone know how to change short text to date/time format to an existing table with date records in it. I have tried this but I get a message saying that 50 records will be deleted if I click ok.

    The table was imported from excel with a date column already in it but didn't have a date/time format just short text format hence I am experiencing this problem.



    Help!

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    I suspect it is not the records being deleted but the field being set to null. The reason is probably because the text value does not conform to a short date.

    If this is a one time conversion, or just as a learning exercise to work out how to handle exceptions for future imports, do the following.

    1. Create a new date/time field in the table - call it say newDate
    2. Create an update query to update newDate with the value from your shorttext field
    3. run it - ignore any errors
    4. open the table and filter it on the newDate column to only show nulls (or sort it if you prefer)
    5. inspect your shortdate columns for the values and then either manually add the dates or modify your update query to cope with them and run it again
    6. Once done, either rename or delete the short text column, then rename the newdate column

    This usually comes about because an excel user has mistyped a date or put something like 'unknown' or 'tbc'

  3. #3
    LovellDC is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Feb 2017
    Posts
    12
    Thank you Ajax, I visually checked the short date column and the date is entered in the same format through out the table as dd.mm.yy hence I didn't understand why 50 would be deleted. Any way I will follow your instructions next week as I am away for a few days now.

    Thank you once again.

  4. #4
    LovellDC is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Feb 2017
    Posts
    12

    changing date field to medium format with input mask but copying the date from the update query

    I followed your instructions which worked but when I went to change the format of the new date to short date, the date changed from what was copied from the original date, I presume this is because I added the format afterwards but when I added the format of short date when adding newDate field it didn't work either so a little confused how to get the original date in short date format. Another thing when changing the format it came back with 62 blanks and I didn't have any blanks when I created the update query.

    Do you know what may have happened?

    I had the newDate copied from the original field which was fine but the whole purpose of this exercise is that I want the date to be formatted in a medium date or I want to add input mask ie 05.04.17 but am struggling.

    Help!


    Quote Originally Posted by Ajax View Post
    I suspect it is not the records being deleted but the field being set to null. The reason is probably because the text value does not conform to a short date.

    If this is a one time conversion, or just as a learning exercise to work out how to handle exceptions for future imports, do the following.

    1. Create a new date/time field in the table - call it say newDate
    2. Create an update query to update newDate with the value from your shorttext field
    3. run it - ignore any errors
    4. open the table and filter it on the newDate column to only show nulls (or sort it if you prefer)
    5. inspect your shortdate columns for the values and then either manually add the dates or modify your update query to cope with them and run it again
    6. Once done, either rename or delete the short text column, then rename the newdate column

    This usually comes about because an excel user has mistyped a date or put something like 'unknown' or 'tbc'

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    This worked for me. The format of the DateDate field in the table is medium date.

    Table Design
    Attachment 28142

    table data before
    Attachment 28143

    update query (run this one time to do the conversion)
    Attachment 28144

    Result
    Attachment 28145
    Last edited by davegri; 04-06-2017 at 09:07 AM. Reason: clarity

  6. #6
    LovellDC is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Feb 2017
    Posts
    12
    Thank you will try this.
    Quote Originally Posted by davegri View Post
    This worked for me. The format of the DateDate field in the table is medium date.

    Table Design
    Attachment 28142

    table data before
    Attachment 28143

    update query
    Attachment 28144

    Result
    Attachment 28145

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    If you want to skip the attempt to update non-date fields, use this query. It will ignore the non-date fields.
    All the other in post #5 stays the same.

    Attachment 28153
    Last edited by davegri; 04-06-2017 at 09:20 AM. Reason: clarity

  8. #8
    LovellDC is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Feb 2017
    Posts
    12
    Many thanks
    Quote Originally Posted by davegri View Post
    If you want to skip the attempt to update non-date fields, use this query. It will ignore the non-date fields.
    All the other in post #5 stays the same.

    Attachment 28153

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-18-2016, 09:46 AM
  2. Short time format problems
    By JeroenMioch in forum Forms
    Replies: 7
    Last Post: 12-09-2015, 01:17 PM
  3. Windows 10 and short date format.
    By NorwegianSiggy in forum Access
    Replies: 1
    Last Post: 09-04-2015, 01:34 PM
  4. Replies: 1
    Last Post: 02-16-2013, 09:11 AM
  5. Dealing with Short Time format
    By hawkins in forum Access
    Replies: 0
    Last Post: 08-16-2011, 11:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums