Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010
    Posts
    29

    Unanswered: Year Total from a "date" and "total"

    I have a graph that is pulling data from a query. The name of the query colums are Total and dateofaudit that I am using for this problem. I enter the number of findings I find in the total column and enter the date of the audit. I want to show how many findings I have for the entire year, either being 2010, 2011 etc.

    Here is what I am trying to do:
    Total for dateofaudit that equals the year 2011 etc.

    dateofaudit Total
    01/10/11 2
    02/12/11 7

    Total for 2011 would be 9.

    I'm not sure if I should do this in the query or report. Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In SQL:
    Code:
    SELECT SUM(Total)
    FROM <Table or Query name>
    WHERE dateofaudit Between #01/01/2011# And #01/01/2012#;
    In VBA:
    Code:
    NumberOfAudits = DSum("Total", "<Table or Query name>", "dateofaudit Between #01/01/2011# And #01/01/2012#")
    Note. You can also specify the criteria as:
    Code:
    DatePart("yyyy", dateofaudit) = 2011
    Have a nice day!

  3. #3
    Join Date
    Nov 2010
    Posts
    29
    This is the code in a text box I am trying in the report but keeps giving me an error:
    =Count(IIf([Total] And [dateofaudit]="2011",0))

    It is in the report footer while the Total and dateofaudit is in the detal section.

    Is there anyway I can use something like this in the Report? I want to show each year in the graph. Thanks!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    IIf() is a three arguments function:
    Code:
    IIf(<Condition>, <Value when True>, <Value when False>)
    1. In your code the condition is:
    Code:
    [Total] AND [dateofaudit] = "2011"
    So you test for:
    Code:
    <Numeric Value> And <Date Value> = <String Value>
    Which is not very coherent, even with implicit conversions!

    2. The <Value when False> part is missing.

    3. Count() operates on a set of values, not on the result of a scalar function.

    4. Count() Returns the number of elements, not the total of their values.
    Have a nice day!

Posting Permissions

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