Results 1 to 5 of 5

Thread: Date Format

  1. #1
    Join Date
    Nov 2007
    Posts
    16

    Unanswered: Date Format

    I'm looking for some values between two particular dates. I have some data in my database that satisfies the following requirements:

    SELECT *
    FROM SaleProperty
    WHERE DatePlacedOnMarket BETWEEN 01/05/2007 AND 30/06/2007;

    When I run this query however, nothing appears on the datasheet. Is this to do with the date format I have used?

    P.S. I have set the DatePlacedOnMarket field to Date/Time format.

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    Try:
    BETWEEN #01/05/2007# AND #30/06/2007#
    The #s tell Access it's dealing with dates. The way you have it, it will think they are numbers and will divide 1 by 5 and then by 2007.

    And depending on your settings you may have to switch the day and month to the American format.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dates in JET SQL can be problematical
    if you have problems then it helpe to encapsulate dates with the hash symbol #
    it also helps to specify US date formats
    so the SQL probably should look like.....

    SELECT *
    FROM SaleProperty
    WHERE DatePlacedOnMarket BETWEEN #05/01/2007# AND #06/30/2007#

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Depends where you are using the statement

    In the query designer GRID try this

    SELECT *
    FROM SaleProperty
    WHERE DatePlacedOnMarket BETWEEN #01/05/2007# AND #30/06/2007#;


    In VBA (or query desiger SQL VIEW) try this

    SELECT *
    FROM SaleProperty
    WHERE DatePlacedOnMarket BETWEEN #05/01/2007# AND #06/30/2007#;

    note American/continetal dates.

    MTB

  5. #5
    Join Date
    Nov 2007
    Posts
    16
    Thanks a lot for your replies, it works now. Indeed it was the fact that I wrote the date the wrong way around and I didn't add the hashes!

    Thanks again

Posting Permissions

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