Results 1 to 6 of 6
  1. #1
    autiger58 is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Posts
    22

    Change Date to number

    This is probably simple but for the life of me I can not figure this out.

    I have a table with 25 fields with one field being a date field named [date1] in a table named TEST with the the format of mm/dd/yyyy. I want to convert the field with an update query from mm/dd/yyyy to mmddyyyy.

    So I start Query Design, Select the TEST Table, Place date1 in the Field: area which populates the Table: area with TEST. In the Update To: area I put Format([date1],"mmddyyyy") of which I get the response from Access that I must have a destination field when I run it. I want to UPDATE the date1 field with the new format and not create a new field.

    Anyone know what I am doing wrong?



    Also, is there going to be a type mismatch error trying to update a field from 00/00/0000 to 00000000?

    Thanks,

    Michael

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Paste this sql into the query design sql view:

    UPDATE Orders SET TEST.[Date1] = Format([Date1],"mmddyyyy")

    HOWEVER, date1 in the TEST table cannot be defined as DATE/TIME. In table design, change it to Text before you run the update query.
    Also in the table design, you will need to remove any validation that requires the date format.

    Now you can run the update query.

  3. #3
    autiger58 is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Posts
    22
    Quote Originally Posted by autiger58 View Post
    This is probably simple but for the life of me I can not figure this out.

    I have a table with 25 fields with one field being a date field named [date1] in a table named TEST with the the format of mm/dd/yyyy. I want to convert the field with an update query from mm/dd/yyyy to mmddyyyy.

    So I start Query Design, Select the TEST Table, Place date1 in the Field: area which populates the Table: area with TEST. In the Update To: area I put Format([date1],"mmddyyyy") of which I get the response from Access that I must have a destination field when I run it. I want to UPDATE the date1 field with the new format and not create a new field.

    Anyone know what I am doing wrong?

    Also, is there going to be a type mismatch error trying to update a field from 00/00/0000 to 00000000?

    Thanks,

    Michael
    Fantastic! It worked like a charm. Thanks!!

    Michael

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Glad to help, Michael. Good luck with your project going forward!
    daveGri

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    autiger58; you realize you're not going to be able to compare these text date values against any real dates, right? At least, not without using a function to try to coerce them back to dates.

  6. #6
    autiger58 is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Posts
    22
    Micron:

    Sorry I missed your response from long ago. Yes, unfortunately, I realize that. The data is for someone else and I have to convert it into their format before sending it to them. I keep the dates in their standard format for as long as I can in order to do date checks and then convert them just before sending the data off. Michael

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

Similar Threads

  1. Replies: 2
    Last Post: 07-15-2014, 12:00 PM
  2. Automatically Change Number In Field
    By Scofield67 in forum Access
    Replies: 4
    Last Post: 05-18-2014, 03:46 PM
  3. VBA - Number to Date Change
    By fpmsi in forum Programming
    Replies: 3
    Last Post: 10-18-2011, 12:06 PM
  4. Replies: 0
    Last Post: 05-07-2011, 04:24 PM
  5. Change Date Into Number
    By greenbag in forum Queries
    Replies: 0
    Last Post: 11-14-2008, 04:43 PM

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