Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating Dates (2000)

    Edited by HansV to prevent <!t>[time]<!/t> from being interpreted as the <img src=/S/time.gif border=0 alt=time width=23 height=39> smiley, using <!t>[t]<!/t> and <!t>[/t]<!/t> tags.

    I have been trying to calculate the dates between two query fields and get an incorrect number. If I calculate the difference between the two days a week apart it is correct, but if i get any further out than that the number returned is incorrect. I have attached the SQL strings of the data. The first SQL string is the original query where the information is being pulled from. I also might want to mention that the dates that are being calculated are inputed fields.

    SELECT QryBackOrder.fcompany AS Customer, QryBackOrder.PartNoRev, QryBackOrder.Qty, QryBackOrder.Sales, QryBackOrder.UnitPrice, QryStdCost.StdCost, [unitprice]-[stdcost] AS StdMargin, [StdMargin]*[Qty] AS TotalStdMargin, [TotalStdMargin]/[Sales] AS [TotalStdMargin%], QryBackOrder.fduedate
    FROM QryBackOrder INNER JOIN QryStdCost ON QryBackOrder.PartNoRev = QryStdCost.PartNoRev
    WHERE (((QryBackOrder.fduedate) Between [Enter Beginning Date] And [Enter finish Date]));

    2nd:
    SELECT Max([*QryWk].fduedate) AS MaxOffduedate
    FROM [*QryWk];

    3rd:SELECT Min([*QryWk].fduedate) AS MinOffduedate
    FROM [*QryWk];

    4th:
    SELECT [MaxOffduedate]-[MinOffduedate] AS <!t>[Time]<!/t>
    FROM QryMaxBoWkDate, QryMinBoWkDate;

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating Dates (2000)

    Welcome to Woody's Lounge.

    Without seeing the database, it's hard to say why the result is off. You don't actually need 4 queries, you can calculate the difference in the second one:

    SELECT Max([*QryWk].fduedate) AS MaxOffduedate, Min([*QryWk].fduedate) AS MinOffduedate, [MaxOffduedate]-[MinOffduedate] As <!t>[Time]<!/t>
    FROM [*QryWk]

    Does that also return incorrect results?

  3. #3
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Dates (2000)

    Thanks alot!

    That worked, I don't know why with the different queries it didn't work?!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating Dates (2000)

    I'm sorry, I don't know why the separate queries wouldn't work - they should, as far as I can see. If you really want to know, you could post a stripped down copy of your database. That would enable Loungers to investigate the problem directly. See <post#=401925>post 401925</post#> for instructions.

Posting Permissions

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