Compare Dates in MS Access

Discussion in 'MS Access' started by coderzone, Jun 2, 2009.

  1. coderzone

    coderzone Super Moderator

    Joined:
    Jul 25, 2004
    Messages:
    736
    Likes Received:
    38
    Trophy Points:
    28
    I would like to compare dates in MS ACCESS

    SELECT *
    FROM Ledger
    WHERE TranDate>1/1/2008 TranDate<1/1/2009
    ORDER BY TranDate;

    But when I use the above it gives no results where as there are lots of records for 2008 and when I use quotes it gives error saying datatype mismatch
     
  2. SaswatPadhi

    SaswatPadhi ~ Б0ЯИ Τ0 С0δЭ ~

    Joined:
    May 5, 2009
    Messages:
    1,342
    Likes Received:
    55
    Trophy Points:
    0
    Occupation:
    STUDENT !
    Location:
    Orissa, INDIA
    Home Page:
    http://www.crackingforfun.blogspot.com
    I think this should work :

    Code:
    SELECT *
    FROM Ledger
    WHERE TranDate BETWEEN #1/1/2008#  AND #1/1/2009#
    ORDER BY TranDate;
    I think you have used Oracle or SQLServer earlier, so you used quotes. But the delimiter in MS Access is # not ' ;)
     
    Last edited: Jun 2, 2009
  3. sriram225

    sriram225 New Member

    Joined:
    May 22, 2009
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    0
    U Haven't used "and" keyword to join both conditions..use it like this..
    SELECT *
    FROM Ledger
    WHERE TranDate>1/1/2008 andTranDate<1/1/2009
    ORDER BY TranDate;


    And Also be Sure that TranDate is Text dataType ......
     
  4. sriram225

    sriram225 New Member

    Joined:
    May 22, 2009
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    0
    U Haven't used "and" keyword to join both conditions..use it like this..
    SELECT *
    FROM Ledger
    WHERE TranDate>#1/1/2008# andTranDate<#1/1/2009#
    ORDER BY TranDate;

    # delimitter must used in ms access
     
  5. SaswatPadhi

    SaswatPadhi ~ Б0ЯИ Τ0 С0δЭ ~

    Joined:
    May 5, 2009
    Messages:
    1,342
    Likes Received:
    55
    Trophy Points:
    0
    Occupation:
    STUDENT !
    Location:
    Orissa, INDIA
    Home Page:
    http://www.crackingforfun.blogspot.com
    That won't work without # delimiter.

    TranDate is obviously Date data-type.

    Sriram, don't be hurried to post.
    Take your time and include all you want to say in one post.
     
    Last edited: Jun 2, 2009
  6. sriram225

    sriram225 New Member

    Joined:
    May 22, 2009
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    0
    Dude Here also same problem............cool KID
     
  7. sriram225

    sriram225 New Member

    Joined:
    May 22, 2009
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    0
    Of course it is date datatype if we don't use # then trandate should be text datatype else if it is date datatype then we have to use #....Cool KID.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice