Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need Query Running Total Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    The following query works except when I try to get a running total the following is returning #Error

    RunTot: Format(DSum("curPAYMENT","tblSumServe2","[strREF]<=" & [Reference] & ""),"$000,000.00")

    What am I doing wrong?

    Thanks, John

    Complete SQL:


    <pre>SELECT tblSumServe2.strREF AS Reference,
    Count(tblSumServe2.idsKeyOfSumServe2) AS [Count],
    Sum(tblSumServe2.curPAYMENT) AS Amounts,
    Format(DSum("curPAYMENT","tblSumServe2","[strREF]<=" & [Reference] & ""),"$000,000.00") AS RunTot
    FROM tblSumServe2
    WHERE ((((tblSumServe2.dteSUMDAY)>=[Enter Start Date: Example m/d/y, or OK for All]
    Or (tblSumServe2.dteSUMDAY) Like [Enter Start Date: Example m/d/y, or OK for All] & "*")
    And ((tblSumServe2.dteSUMDAY)<=[Enter End Date: Example m/d/y, or OK for All]
    Or (tblSumServe2.dteSUMDAY) Like [Enter End Date: Example m/d/y, or OK for All] & "*"))
    AND (((tblSumServe2.strBatchNo)>=[Enter Start Batch Number or OK for All]
    Or (tblSumServe2.strBatchNo) Like [Enter Start Batch Number or OK for All] & "*")
    And ((tblSumServe2.strBatchNo)<=[Enter End Batch Number or OK for All]
    Or (tblSumServe2.strBatchNo) Like [Enter End Batch Number or OK for All] & "*")))
    GROUP BY tblSumServe2.strREF;
    </pre>


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

    Re: Need Query Running Total Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Is strRef a text field, as the str prefix indicates? If so, you should enclose the value in quotes, as always. Try

    RunTot: Format(DSum("curPAYMENT","tblSumServe2","[strREF]<=" & Chr(34) & [Reference] & Chr(34)),"$000,000.00")

    If that doesn't work, the alias Reference may not be recognized; try

    RunTot: Format(DSum("curPAYMENT","tblSumServe2","[strREF]<=" & Chr(34) & [strREF] & Chr(34)),"$000,000.00")

    HTH

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Query Running Total Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Thanks Hans

    They both worked.

    John

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Query Running Total Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Hi Hans

    My running sum is summing all records in the table.

    How doI restrict my summing to just the records isolated by my whare clause?

    Which in my case is Amounts: curPAYMENT

    Thanks, John

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

    Re: Need Query Running Total Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    You already have a where clause in your DSum. We would have to see the database (with some dummy data) to find out what goes wrong.

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Query Running Total Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    If you run the query and click OK 4 times you will get totals of all the records.

    if you run the query for 3/1/4 thru 3/31/4 you will see the running totals are for all the records. I want running totals to include the March amounts.

    John
    Attached Files Attached Files

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

    Re: Need Query Running Total Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    You will have to work the where-condition of the query into the where-condition of the DSum. It now onlt puts a restriction on strREF, but not on the dates and batch numbers.

Posting Permissions

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