Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: Count Function Issue

    =DSum("[CountMvp]","[Page 5 Matrixs]","1")

    This function that's in a text box is summing the entire column and not the values that = 1.
    This function is located in a report that is tied to the Page 5 Matrix. I don't understand why it doesn't see the "1". I tried Like "1" and = "1", but nothing works. Any ideas?

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    try "[CountMvp]=1"
    or "[CountMvp] = '1'" if it's a text field

  3. #3
    Join Date
    Mar 2004
    Posts
    361
    Maybe this will help. This is the function in the query that is associated with the report.

    CountMvp: IIf([Acct Status]="3" And [Date_Sold] Between #8/1/2003# And #8/31/2003#,-1,IIf([Acct Status]="3" And [Date_Sold] Between #9/1/2003# And #9/30/2003#,2,IIf([Acct Status]="3" And [Date_Sold] Between #10/1/2003# And #10/31/2003#,3,IIf([Acct Status]="3" And [Date_Sold] Between #11/1/2003# And #11/30/2003#,4,IIf([Acct Status]="3" And [Date_Sold] Between #12/1/2003# And #12/31/2003#,5,IIf([Acct Status]="3" And [Date_Sold] Between #1/1/2004# And #1/31/2004#,6,IIf([Acct Status]="3" And [Date_Sold] Between #2/1/2004# And #2/29/2004#,7,IIf([Acct Status]="3" And [Date_Sold] Between #3/1/2004# And #3/31/2004#,8,IIf([Acct Status]="3" And [Date_Sold] Between #4/1/2004# And #4/30/2004#,9,IIf([Acct Status]="3" And [Date_Sold] Between #5/1/2004# And #5/31/2004#,10,IIf([Acct Status]="3" And [Date_Sold] Between #6/1/2004# And #6/30/2004#,11,IIf([Acct Status]="3" And [Date_Sold] Between #7/1/2004# And #4/3/2003#,12))))))))))))

    Any other ideas????

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    What is the name of the control on the report that contains the value you want to count?

    Do you only want to count this value if it equals "1"?

    Is the CountMvp field in your query formatted as numeric or text?

    What section of the report do you want to display the count on?

  5. #5
    Join Date
    Mar 2004
    Posts
    361
    1) The name of the control on the report I want populated is txtMvpAug

    2) I only want to count if the value is 1. On a separate text box I will count if the value is 2 so on and so on.

    3) I'm not sure. Where would I look to find this out? I would guess it's numeric but I'm not sure.

    4) These values will be in the Page Header

  6. #6
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    I haven't tried this in a header (I usually total data in the footer), but give this a shot.....

    in a text box in the header set the control source

    =sum(iif([txtMvpAug] = 1,1,0))

    *** you can check the properties of a query field by right clicking the field in the query's design view

  7. #7
    Join Date
    Mar 2004
    Posts
    361
    I tried to right click on the field CountMvp in the query in design mode and the properties are blank. It just gave me two tabs. General and Lookup.

    This is the only function that is getting a value right now.

    =DSum("[CountMvp]","[Page 5 Matrixs]","1")

    Again it is summing the comlum versus summing (or I would rather count) the value = to 1.

  8. #8
    Join Date
    Mar 2004
    Posts
    361
    BRAKES. I got it. How do I get the value to be level with all of my labels in the page header section.

  9. #9
    Join Date
    Mar 2004
    Posts
    361
    Nope I got it. Thanks a ton.

Posting Permissions

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