Results 1 to 7 of 7

Query to change YYYYMMDD to MMDDYYYY

This is a discussion on Query to change YYYYMMDD to MMDDYYYY within the Microsoft Access forums, part of the Question Forums category; What would the syntax be? I want to query field 1 text with 20070101; convert it and have a date ...

  1. #1
    New Member
    Join Date
    Mar 2005
    Location
    Portland, OR
    Posts
    18

    Default Query to change YYYYMMDD to MMDDYYYY

    What would the syntax be?
    I want to query field 1 text with 20070101; convert it and have a date format of 01/01/2007 in the results.

    Thanks

  2. #2
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,198

    Default

    try...
    =DateSerial(Left([Field1],4),Mid([Field1],5,2),Right([Field1],2)

    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  3. #3
    New Member
    Join Date
    Mar 2005
    Location
    Portland, OR
    Posts
    18

    Default Thank you, SydneyGeek!~

    I appreciate it. Works perfectly.

  4. #4
    Board Regular
    Join Date
    Jan 2007
    Posts
    353

    Default Re: Query to change YYYYMMDD to MMDDYYYY

    Quote Originally Posted by SydneyGeek View Post
    try...
    =DateSerial(Left([Field1],4),Mid([Field1],5,2),Right([Field1],2)

    Denis
    Where do I place this formula? Is it in the Criteria Field or create a new field in the query?

  5. #5
    Board Regular
    Join Date
    Jan 2009
    Posts
    867

    Default Re: Query to change YYYYMMDD to MMDDYYYY

    new field in a query

  6. #6
    Board Regular
    Join Date
    Jan 2007
    Posts
    353

    Default Re: Query to change YYYYMMDD to MMDDYYYY

    Thanks. It works

  7. #7
    Board Regular
    Join Date
    Jan 2007
    Posts
    353

    Default Re: Query to change YYYYMMDD to MMDDYYYY

    Could you please help me to create a field in Access Query for the serial number for each row.

    Thanks.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com