If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Counting number of records based on criteria
Hello,
In a report I'm using in a reportfield the expression =Count(*) which gives me the total number of records showed in this report. I'm also want to know from a specific datafield in this report how many of them are showed. Something like =Count([Field1]="999") In words.... count the number of fields where the registered data is 999 When I'm using it as described above, then the output gave me the same number as with =Count(*) What's wrong ? Please help me out. regards, Johan |
#2
|
|||
|
|||
Counting number of records based on criteria
Hello Johan,
the Count() function is only for counting not-null-values (or complete lines with *). It is not designed to be used with an condition. Your condition evaluates something (it doesn't matter if the result is true or false) which is counted. Either use the DCount function instead which is independent(!) to your report =DCount("some field"; "table/query";"[Field1]=999") but note that if you are using groups or filterted data in your report you should add these creteria to the third parameter. Or you use a hidden helper field in your details section: Name: Helper1 ControlSource: =CLng([Field1]=999)*-1 Visible=False In the report footer (or any group footer) you then can refer to the count by using =Sum([Helper1]). Note that Helper1 evaluates your condition and converts the logical True/False to numerical (CLng) -1/0. These values then must be sumarized. Yours Alexander "johan" schrieb im Newsbeitrag ... Hello, In a report I'm using in a reportfield the expression =Count(*) which gives me the total number of records showed in this report. I'm also want to know from a specific datafield in this report how many of them are showed. Something like =Count([Field1]="999") In words.... count the number of fields where the registered data is 999 When I'm using it as described above, then the output gave me the same number as with =Count(*) What's wrong ? Please help me out. regards, Johan |
#3
|
|||
|
|||
Counting number of records based on criteria
You can do what you want using
=Abs(Sum([Field1]="999")) Or =Count(IIF([Field1]="999",1,Null)) The expression [Field1]="999" will return True or False. True is equal to -1 and false is equal to zero. So the sum of those values is equal to a negative count of the records where the condition is met. Abs removes the negative sign. Count counts the presence of any value that is NOT null, the the IIF function returns null when the expression is false. Again you get an accurate count of the trues since 1 is a non-null value. You could put "X" or 2000 in as the second argument to the IIF and get the same result. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County johan wrote: Hello, In a report I'm using in a reportfield the expression =Count(*) which gives me the total number of records showed in this report. I'm also want to know from a specific datafield in this report how many of them are showed. Something like =Count([Field1]="999") In words.... count the number of fields where the registered data is 999 When I'm using it as described above, then the output gave me the same number as with =Count(*) What's wrong ? Please help me out. regards, Johan |
Thread Tools | |
Display Modes | |
|
|