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.
15 11531
Why don't you try the DCount() function? - =DCount("[Status]", "[YourTableName]", "[Status] = 'Y'")
Regards,
Scott
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.
I realize this has something to do with COUNT counting all records even if Null. I tried - =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. :-)
Why don't you try the DCount() function? - =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?
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 - AND [District] = [SomeValue]
Regards,
Scott
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 - 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?
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
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. -
=DCount("[SubDate]","OperationPlan","[Status]='Y' " And " [District]=[*]")
-
-
=DCount("[SubDate]","OperationPlan","[Status]='Y' " And " [District]=' '")
-
-
=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.
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: - 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
Thanks Scott, it's time for a long weekend so I'll let you know how it turns out.
Have a good weekend!
Good luck with it...
Regards,
Scott
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.
Please post the code you have now that isn't working :-)
Thanks!
Regards,
Scott
Please post the code you have now that isn't working :-)
Thanks!
Regards,
Scott
If I try this I get an error# -
=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. -
=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
and another with - =DCount("[SubDate]","OperationPlan","[Status]='Y' " And " [District]=[*]")
Then I divide the two to get the % using - =([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.
If I try this I get an error# -
=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. -
=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
and another with - =DCount("[SubDate]","OperationPlan","[Status]='Y' " And " [District]=[*]")
Then I divide the two to get the % using - =([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 - =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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Simon Matthews |
last post by:
Hope someone can help an Access beginner!
I've just started keeping my surgical logbook on access and it's a
simple flat-file affair. I have created several queries that will
list cases...
|
by: hungrymind |
last post by:
Hi all,
I am developing some control (textbox based), to validate inputs to
that control I am using regular expression, where pattern is generated
dynamically. I need to identify what all...
|
by: lucky |
last post by:
hi,
i got file which contains "----------------" in a line. the line only
contains this data as a saperation. using regular expression i want to
i detify the line contains that data and replace...
|
by: david.kao |
last post by:
As an IT developer, in past twelve years I spent great deal amount of time
writing different kinds of reports. In past, as a VB developer I gave up
Crystal Report about seven years ago. I was using...
|
by: Zorik |
last post by:
I am building a form in asp.net 2.0
On one of the textboxes, I don't want that the user will use the space
character.
How do I disallow space using regular expression validator (or other...
|
by: AtCor |
last post by:
I am trying to filter data using count. For a given day and patient, I would like to return all the valid tests. When I use a count this way, it only returns the patients with at least 4 tests. I...
|
by: Dave S |
last post by:
I am currently in the process of converting an MDB to ADP linked to a
SQL Server back end. In the MDB I have expressions in both queries
and report controls that are essentially:
...
|
by: durga2005 |
last post by:
hi
i just added one textbox and regular expression validator in the form and set the properties for regular expression validator control.
then i placed these two controls inside update panel ....
|
by: Waganjika |
last post by:
How can I code preview a report designed using c# crystal report?
The Application builds successfully, and all other functions works alright but when I select from menu to preview a report,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |