Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Question Unanswered: Problem with SQL date range

    Hi

    I am using Access 2000, and I am trying to write a SQL string but I can't work out how to do it. The problem is, I need to take an input range of dates and apply some math to it, but I can't work out how to do it for a range of dates.

    For a single date an example would be:

    SELECT
    date,
    (((field1/(SELECT (field1 FROM tbl1 WHERE date = #01/01/04#) - 1) * 100)
    FROM tbl1 WHERE date = #01/01/04#

    How do I write this query so that it will work on each of a range of dates?

    Thank you
    Make something idiot proof and someone will make a better idiot...

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Providing that the sub-query returns only 1 row, then

    SELECT date, (field1 / (SELECT field1-1 FROM tbl1 WHERE date = #01/01/04#) * 100) FROM tbl1 WHERE date = #01/01/04#
    Last edited by r123456; 07-22-04 at 14:08.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    That's the crux of the problem - I want to be able to run this for a range of dates, and as it is written, the subquery should only return one row, so I don't know what to do for a range of dates.
    Make something idiot proof and someone will make a better idiot...

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Please disregard my previous suggestion.

    SELECT date, (field1 / (field1-1) * 100)
    FROM tbl1
    WHERE date = BETWEEN #01/01/04# AND #01/10/04#
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by r123456
    Please disregard my previous suggestion.

    SELECT date, (field1 / (field1-1) * 100)
    FROM tbl1
    WHERE date = BETWEEN #01/01/04# AND #01/10/04#
    Correction:

    WHERE (date BETWEEN #01/01/04# AND #01/10/04#)

    ALSO "date" is a reserved word ... Better pick a different name or qualify this one (i.e. make it:[date]) ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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