This seems like it should be really easy and I think I'm just missing something really dumb.
I have a form that filters a report. At the top of the report I have a bunch of unbound text boxes that do simple things like count and sum. When I apply the filter to the report the text boxes update beautifully when their control source is something like
However now I want to apply a clause. If I do this: - =dcount("[num hatched]", "[tbl_nesting]","[num hatched]>0")
then I get a number but it's ALWAYS that number despite the filter. I want it just to count what's displayed after the filter but
doesn't work, just reports a false count.
What am I doing wrong?
7 5525 NeoPa 32,577
Recognized Expert Moderator MVP
There's quite a difference between the Aggregate functions and the Domain Aggregate functions.
In a report a Domain Aggregate function (DCount()) is self-contained. You must specify the record source etc. With an Aggregate function (Sum()) it refers to controls accessible (generally in the Detail section) in your report. Not fields notice (which are references to the underlying data source), but the controls.
NeoPa 32,577
Recognized Expert Moderator MVP
You also mention filtering the report. As far as I'm aware, filtering is always applied to the recordsource, and is never effected by the values in controls. Does this help to understand?
Kind of. I had figured out that Count was going to work better than DCount, but I can't figure out how to phrase the statement I want with the rule applied (e.g. count, which works, vs. count where field value > 0, which doesn't work). Can you apply a rule like that in an aggregate function? I am useless with the expression builder - it is not very intuitive to me.
Got it. I needed an IIF statement. - =Count(IIf([Num Hatched]>0,0))
NeoPa 32,577
Recognized Expert Moderator MVP AccessIdiot: - =Count(IIf([Num Hatched]>0,0))
You did, but I suggest one more like : - =Count(IIf([Num Hatched]>0,1,0))
Can you translate that into a full sentence for me? I didn't fully understand even in the one that worked for me why 0 was used for "true" and not 1. In my binary world we always use 0 for false an 1 for true.
So my own example would read:
"If num hatched > 0, then false" which obviously isn't the case. And I'd read your statement to read:
"If num hatched > 0, then true, else false."
So why do the 0's and 1's get switched around? Of is mine just shorthand where the first/only comma is the else statement? Did I just answer my own question lol?
Actually A your statement didn't work? Now I'm doubly confused.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Iyhamid |
last post by:
Hi
I am looking for codes to filter and apply filter to my data base using access
|
by: mattscho |
last post by:
Hi All, Trying to create a set of 3 buttons in a form that have the same effect as the "Filter by Form", "Apply Filter" and "Remove Filter" Buttons on the access toolbar. Help would be muchly appreciated. Cheers.
|
by: mattscho |
last post by:
Re: Filter By From, Apply Filter, Remove Filter Buttons in a Form.
--------------------------------------------------------------------------------
Hi All, Trying to create a set of 3 buttons in a form that have the same effect as the "Filter by Form", "Apply Filter" and "Remove Filter" Buttons on the access toolbar. Help would be muchly appreciated. Cheers.
In the Click() Event of 3 Command Buttons, place the following code:
Code: (...
|
by: Fran |
last post by:
I recently tried to use code for "Use a multi-select list box to
filter a report" from Allen Browne in my database. I was able to add
the code and adapt it to my needs, however I am getting an error.
I click Ok and the report opens.
I look in the design view and the filter is there but the IN equals
"" (it is blank, the values are not in there)
|
by: Supermansteel |
last post by:
I am trying to run a Apply filter for everytime someone opens Form_CC it will only show the Test (Test_ID) they are working on. This seems to be the closest I have gotten to filtering it correctly, however it doesn't work when the form is opened. Is there something I am doing wrong on this?
Private Sub Form_Open(Cancel As Integer)
DoCmd.ApplyFilter Form_CC.Form.filter = "Test_ID = 28"
Form_CC.Form.FilterOn = True
End Sub
| |
by: Gord |
last post by:
I'm trying to filter a report with a date in VB code.
If I type an actual date bracketed with the pound symbol (i.e. #3-Jul-08#)
I can get the filter to work. I can't seem to get it to work by using a
variable. Am I trying to do the impossible?
I've tried using a date variable that I know contains a valid date. I've
tried bracketing it with pound symbols, single quotations etc. I've tried a
string variable and used the 'CDate'...
|
by: eHaak |
last post by:
A couple years ago, I built a database in MS Access 2003. I built the form using macros in some of the command buttons, and now I’m trying to eliminate the macros and just use visual basic code. I’ve been successful in doing this for most of the buttons, but I’m having trouble reprogramming some Apply Filter buttons on one form and could use some help.
So I have a Contacts List form that lists all of the division’s staff members...
|
by: dbdb |
last post by:
hi guys
need your suggestion
how can i apply filter for my date variable data type
i have a form name transaction
and i have a text box on it named : start and finish
i need to apply filter my data using that 2 data.
i have to view data for the transaction date between start and finish date.
|
by: dbdb |
last post by:
Hi,
i create a chart in ms access based on my query, then i want my chart when is it open is only show value based on my criteria.
i'll try to used it in the properties apply filter using the expression, it didn't work. the chart still viewing all data.
and i used the event "on open" and used "applyfilter" command
then it shown an error.
"that the report isn't bound with the query"
|
by: Azriel928 |
last post by:
I know this is a simple function to program, but I'm a little rusty. I'm trying to set the "On Open" event on a report using a macro to apply a filter that I have saved as a query.
Can you please provide an example of how I should enter the information to run this filter when I open the report.
Thanks,
Ashley
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |