Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Posts
    19

    Unanswered: Date not picked up

    Hi

    Why will this query not find data in the database that has today’s date (01/09/2008). The query will show information for previous dates.

    Code:
    "SELECT ProjID FROM Table1 WHERE Table1.hDate Between #26/08/2008# AND #01/09/2008#"
    Thanks

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Because SQL uses Americian format dates.

    Try

    SELECT ProjID FROM Table1 WHERE Table1.hDate Between #8/26/2008# AND #9/1/2008#
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just to clear any ambiguity from above
    Quote Originally Posted by StarTrekker
    Because SQL uses Americian format dates.
    By default, JET SQL uses American dates.
    George
    Home | Blog

  4. #4
    Join Date
    Aug 2008
    Posts
    19
    OH.
    So am i right in thinking that data can be added to Access database using format (dd/mm/yyyy) but can only be retrieved using the (mm/dd/yyyy) format.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    US date format will be a problem..
    infering form your question its working OK on dates before today.....

    then I also suspect that if you want something for today AND you have used a time (Access/JET stores dates as a DATE/TIME value) then you need to specify a time component.

    if you don't specify a time then the time is assumed to be 00:00:00 (ie midnight)

    it also depends on how you have populated your date column.. if you used date() then only the date component would have been stored.. if you used now() then the date AND time components woudl have been stored

    so your options are to....
    specify a time, using either now()
    or
    adding a time of say 23:59:59 to the upper limit of your date bracket
    or
    alternatively add a day to the date bracket
    where mydatevalue between #01/jan/2008# and dateadd("d",adatevalue,1)

    specify now() in place of the date
    eg where mydatevalue between #01/jan/2008# and now()


    make sure you use date() function to store the date if you don't need the time component.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

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