Query to change YYYYMMDD to MMDDYYYY

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Query to change YYYYMMDD to MMDDYYYY

  1. #1
    New Member
    Join Date
    Mar 2005
    Location
    Portland, OR
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Thank you, SydneyGeek!~

    I appreciate it. Works perfectly.

  4. #4
    Board Regular
    Join Date
    Jan 2007
    Posts
    450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    1,096
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Query to change YYYYMMDD to MMDDYYYY

    new field in a query

  6. #6
    Board Regular
    Join Date
    Jan 2007
    Posts
    450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Query to change YYYYMMDD to MMDDYYYY

    Thanks. It works

  7. #7
    Board Regular
    Join Date
    Jan 2007
    Posts
    450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

User Tag List

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