How do I change the format of a query field?

Posted on 2009-12-22
Last Modified: 2012-05-08
Hi everyone,

I am creating a simple query that takes a list of clients with additional related information. Among the fields that I am adding into my query, one field contains dates that do not have a date format.

Their current format is YYYYMMDD but Access does not recognize it as a date.

How can I use the query so that my dates become actual dates in Access(so that I can compare these dates with the actual current date)?

Thank you!
Question by:appc
    LVL 41

    Accepted Solution

    You may use some conversion, e.g.:

    CDate(LEFT(YourField, 4) + "-" + Mid(YourFiled, 5, 2) + "-" + Mid(YourFiled, 7, 2))
    LVL 41

    Assisted Solution

    Use this
    select Format(DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2)),"dd-mm-yyyy")
    LVL 46

    Assisted Solution

    CDate(LEFT(Field1, 4) & "/" + Mid(Field1, 5, 2) & "/" & Right(Field1,2))

    LVL 41

    Assisted Solution

    I would also try

    DateValue(LEFT(TxtField, 4) + "." + Mid(TxtField, 5, 2) + "." + Mid(TxtField, 7, 2))

    Author Comment

    Thanks a lot guys.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video discusses moving either the default database or any database to a new volume.

    779 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

    13 Experts available now in Live!

    Get 1:1 Help Now