Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7

    Unanswered: Only Sum the Postive Number in a report

    I can get the report To sum a colum of numbers but the boss only want the postive numbers sum up ias there a quck way of doing this.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  2. #2
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    hi, once it is at the report level, i don't think there is much you can do about it, though this is what i would do;

    Create a new query;

    drag the field you want to calcualte the sum of into it (eg total1)

    set the criteria as >0

    click the sigma symbol (a backwards E) and select sum from the new row that appears.

    To then add this field to the report you already have;

    create a new report;

    just add in this field from this query

    Then add it into the report you already have as a subreport.

    If there is a way to filter records in reports then you could try that way by setting the filter criteria as >0, but i don't know if that's possible in a report.

    Hope this helps;

    dan
    sometimes simple is best.... and i'm just a simple fellow.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    WAY too difficult Dan ...

    Can you mofidy the report? If so, then change the textbox control source to be a calculation like: SUM(IIF([MyColumn]>0,[MyColumn],0)
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Quote Originally Posted by M Owen
    WAY too difficult Dan ...

    Can you mofidy the report? If so, then change the textbox control source to be a calculation like: SUM(IIF([MyColumn]>0,[MyColumn],0)
    Thanks grand poobah! I knew there would be a way to set criteria in a report, just didn't know how. Oh well my option does seem long winded, but, if we didn't have your tatents it woiuld have had to sufficied!

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Thanks will try it when I get home
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    another alternative is to put some proicessing in the forms detail.format evet, and bin records there if they don't meet certain criteria. you cna also use this for doing some subtotalling or other processing.

    sounds an odd way of doing it, but I have had some situations where the users wants to use a common query & report style, but doesn't want to change the SQL. (in one event becuase it was an external SQL call and the users didn't have the ability to change the SQL, another they didn't want to introduxce more changes to a complex system.)

    the other advantage is that if you are doing range calculations (ie no in range 1>10,11>20,etc) its a simple process int he the report footer(s) to introduce mulitrple subtotals without access havign to issue multiple SQL aggregate functions - ie its a lot quicker.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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