I've been able to get all items calculating correctly except for one. In the District footer for the following example if I use Count or DCount I get a number that does mean anything. If I use Sum I get the correct number but it is a negative value. Both the Status and Subdate fields are text so I don't know how it is summing and giving the correct number. Any ideas on how I can get rid of the negative?
This is the code that gives...
Bernice J
Last Activity: Mar 31 '08, 02:28 PM
Joined: Mar 17 '08
Location:
-
This is getting there. However I'm not counting districts.I want to Group by District and have for each District the Percent of all records with a Subdate and the Percent of records with a SubDate and Status=Y
I will try to modify your code and see what happens. Thanks....Leave a comment:
-
I was able to figure out the SubDate null values using the codeNow I just need to figure out why the values are negative and I should be done....Code:=Sum(([STATUS]='Y') And ([SUBDATE] Is Not Null))
Leave a comment:
-
If I try this I get an error#
If I try this Access automatically puts square brackets around "Me" and then shows a parameter error when I try to preview the report.Code:=DCount("[SubDate]","OperationPlan","(([Status]='Y') AND ([District]= " & [txtDistrict] & ")")
...Code:=DCount("[SubDate]","OperationPlan","(([Status]='Y') ANDLeave a comment:
-
I'm not having much luck. It doesn't recognize "Me" and I notice the Expression builder is either adding square brackets to the "Me" or removing the square brackets from the "TextBoxNam e" when I save it....Leave a comment:
-
Hi,
I was able to create two queries. One that showed all blocks with a subdate one that showed all blocks with a subdate and a status=y. However when I create the report based on the queries and ask it to show me the percent of blocks with a subdate OR the percent of blocks with a subdate and status=y it bases the percent on the total of the query not on the total of all the blocks in the database.
Understand? Anyway here is the...Leave a comment:
-
Thanks Scott, it's time for a long weekend so I'll let you know how it turns out.Leave a comment:
-
Here are 3 lines of code I've been trying. The first two are similar but give the same value. I used a wildcard for the District because I have more than one value for the District field. I do realize this is probably messing things up but I'm in the dark what to do and I just keep reading and testing.
The third one is code I found in a post with a similar problem. I don't understand it obviously because I get a parameter error for...Leave a comment:
-
I have this code in the District footer because I want the total count by District. If I put in the code you suggested I get a count of ALL my records not just those for the Group. Any other ideas?...Leave a comment:
-
Sorry, I didn't see your reply when I replied to myself. Any suggestions on the second part of my own reply?...Leave a comment:
-
I realize this has something to do with COUNT counting all records even if Null. I triedand this worked better. I have this in a control box in a footer for the Group District. It is giving me a total for all records but what I really wanted is to have the total by district. I know I must have to put a reference to that in the expression but I'm unsure how. Maybe if I keep talking to...Code:=DCount([SubDate],"OpPlan","[Status]='Y'")
Leave a comment:
-
Using Count and IIf expression in a report control box
I'm trying to count the number of records in a table with the field STATUS that have a Y value. I'm using the expression "=Count(IIf ([Status]="Y",1,0))" . It counts all the values not just the Y's. Any ideas where I'm going wrong? The field has text values in the table.
I'm using this expression in a textbox in a report. -
Finding Totals for creating percentages in reports
I have a table with four fields: BlockNo, District, SubDate, Status. I would like to create a report that 1)shows the percent of BlockNo that has a SubDate(IsNotNu ll) grouped by District and 2) shows the percent of BlockNo with SubDate(IsNotNu ll) and with Status=Y grouped by District.
I was able to create queries for both of these and count the number of blocks in the query. What I am unable to figure out is how to refer to the TOTAL BlockNo...
No activity results to display
Leave a comment: