By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,010 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Using Count and IIf expression in a report control box

P: 13
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.
Mar 20 '08 #1
Share this Question
Share on Google+
15 Replies


Scott Price
Expert 100+
P: 1,384
Why don't you try the DCount() function?

Expand|Select|Wrap|Line Numbers
  1. =DCount("[Status]", "[YourTableName]", "[Status] = 'Y'")
Regards,
Scott
Mar 20 '08 #2

P: 13
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
Expand|Select|Wrap|Line Numbers
  1. =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.
I realize this has something to do with COUNT counting all records even if Null. I tried
Expand|Select|Wrap|Line Numbers
  1.  =DCount([SubDate],"OpPlan","[Status]='Y'")
and 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 myself in this forum I'll figure it out. :-)
Mar 20 '08 #3

P: 13
Why don't you try the DCount() function?

Expand|Select|Wrap|Line Numbers
  1. =DCount("[Status]", "[YourTableName]", "[Status] = 'Y'")
Regards,
Scott
Sorry, I didn't see your reply when I replied to myself. Any suggestions on the second part of my own reply?
Mar 20 '08 #4

Scott Price
Expert 100+
P: 1,384
The portion after the third comma in the DCount() expression works just like the WHERE clause of a SELECT query, without the WHERE keyword. Thus you can just add another criteria in addition the = 'Y', something like
Expand|Select|Wrap|Line Numbers
  1. AND [District] = [SomeValue]
Regards,
Scott
Mar 20 '08 #5

P: 13
The portion after the third comma in the DCount() expression works just like the WHERE clause of a SELECT query, without the WHERE keyword. Thus you can just add another criteria in addition the = 'Y', something like
Expand|Select|Wrap|Line Numbers
  1. AND [District] = [SomeValue]
Regards,
Scott

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?
Mar 20 '08 #6

Scott Price
Expert 100+
P: 1,384
I find it a little hard to believe that further restriction in the WHERE section of DCount() results in LESS restriction!

Please post the code that is giving you ALL records.

Regards,
Scott
Mar 20 '08 #7

P: 13
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 "txtDistrict" and I thought the txt just meant it would give the string District.

Expand|Select|Wrap|Line Numbers
  1. =DCount("[SubDate]","OperationPlan","[Status]='Y' " And " [District]=[*]")
  2.  
  3. =DCount("[SubDate]","OperationPlan","[Status]='Y' " And " [District]=' '")
  4.  
  5. =DCount("[SubDate]","OperationPlan","(([District]=' "&[txtDistrict]&" ')AND ([Status]='Y'))") 
What I'm trying to do is count all the records with a submission date (SubDate) that also have a Status =Y and get a total by the group District. I have the control box in the District footer.
Mar 20 '08 #8

Scott Price
Expert 100+
P: 1,384
In the detail section (or somewhere) of your report you should have a text box that shows what district the results are for. Set the reference to the name of this text box. The third example you showed is close to what it will look like.

Example:

Expand|Select|Wrap|Line Numbers
  1. AND [District] = " & Me.[TextBoxName] & "
Make sure, of course that [District] reflects the name of the column in your query resultset, and [TextBoxName] reflects the name of the text box on your report.

Regards,
Scott
Mar 20 '08 #9

P: 13
Thanks Scott, it's time for a long weekend so I'll let you know how it turns out.
Mar 20 '08 #10

Scott Price
Expert 100+
P: 1,384
Have a good weekend!

Good luck with it...

Regards,
Scott
Mar 20 '08 #11

P: 13
Have a good weekend!

Good luck with it...

Regards,
Scott

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 "TextBoxName" when I save it.
Mar 25 '08 #12

Scott Price
Expert 100+
P: 1,384
Please post the code you have now that isn't working :-)

Thanks!

Regards,
Scott
Mar 27 '08 #13

P: 13
Please post the code you have now that isn't working :-)

Thanks!

Regards,
Scott

If I try this I get an error#

Expand|Select|Wrap|Line Numbers
  1. =DCount("[SubDate]","OperationPlan","(([Status]='Y') AND ([District]= " & [txtDistrict] & ")")
If I try this Access automatically puts square brackets around "Me" and then shows a parameter error when I try to preview the report.
Expand|Select|Wrap|Line Numbers
  1. =DCount("[SubDate]","OperationPlan","(([Status]='Y') AND ([District]= " & Me.[txtDistrict] & ")")
I am able to get close to what I need by adding to the District Footer one textbox code
Expand|Select|Wrap|Line Numbers
  1. =Sum([STATUS]='Y')
and another with
Expand|Select|Wrap|Line Numbers
  1. =DCount("[SubDate]","OperationPlan","[Status]='Y' " And " [District]=[*]")
Then I divide the two to get the % using
Expand|Select|Wrap|Line Numbers
  1. =([txtSumofStatusY])/([txtDCountSubdateStatusY])
However for some reason I am getting a negative value for the first textbox and this carries through to the %. Another issue is that SubDate has some null values and I don't want the Status counted for those null values.
In another posting I explain the same problem but I used a query to select out everything. I couldn't get it to do a percent based on the total records in the datebase so I tho't I'd try doing it by going directly to the report. The more I do this the more confused I get.
Mar 27 '08 #14

P: 13
If I try this I get an error#

Expand|Select|Wrap|Line Numbers
  1. =DCount("[SubDate]","OperationPlan","(([Status]='Y') AND ([District]= " & [txtDistrict] & ")")
If I try this Access automatically puts square brackets around "Me" and then shows a parameter error when I try to preview the report.
Expand|Select|Wrap|Line Numbers
  1. =DCount("[SubDate]","OperationPlan","(([Status]='Y') AND ([District]= " & Me.[txtDistrict] & ")")
I am able to get close to what I need by adding to the District Footer one textbox code
Expand|Select|Wrap|Line Numbers
  1. =Sum([STATUS]='Y')
and another with
Expand|Select|Wrap|Line Numbers
  1. =DCount("[SubDate]","OperationPlan","[Status]='Y' " And " [District]=[*]")
Then I divide the two to get the % using
Expand|Select|Wrap|Line Numbers
  1. =([txtSumofStatusY])/([txtDCountSubdateStatusY])
However for some reason I am getting a negative value for the first textbox and this carries through to the %. Another issue is that SubDate has some null values and I don't want the Status counted for those null values.
In another posting I explain the same problem but I used a query to select out everything. I couldn't get it to do a percent based on the total records in the datebase so I tho't I'd try doing it by going directly to the report. The more I do this the more confused I get.
I was able to figure out the SubDate null values using the code
Expand|Select|Wrap|Line Numbers
  1. =Sum(([STATUS]='Y') And ([SUBDATE] Is Not Null))
Now I just need to figure out why the values are negative and I should be done.
Mar 27 '08 #15

Scott Price
Expert 100+
P: 1,384
See my reply in Finding Totals for Percentages in Reports.

Kind regards,
Scott
Mar 27 '08 #16

Post your reply

Sign in to post your reply or Sign up for a free account.