Access Equivalent to Excel's COUNTIF function??

[cliffhanger9]

HI All-

The title sorta sums this one up..

What is the best way in Access 2000 (expression builder) to replicate the COUNTIF functionality of Excel

In case you are not familiar with this, it essentially scans a field and counts the number of a specified instance.

So my text box calculates the number of days since a date so the values look like:

12
356
234
47
182
25
93
...any positive number basically. What I am trying to do in another text box (this is all on a report, by the way) is count the number of instances where the value is less than 45 days.

From this number I will determine what percentage of the entire list that count is (if there were 100 rows, and 22 of them had values less than 45 days, this text box would return 22%)

Any help would be appreciated with either bit!!!!!!!
THANKS!!!!!!!!!!!!!!

 

[Crowley16]

erm...

sum(iif(logicalTest, valTrue, valFalse))

I think that'll work

--------------------
Procrastinate Now!

 

[cliffhanger9]

then what do you put for the valTrue/valFalse????

 

[Crowley16]

well, considering you are summing the number of occurrences, then 1, and 0 seems sensible, but you can put any numbers in there if you are counting different things...

--------------------
Procrastinate Now!

 

[cliffhanger9]

yea i tried that but it continues to return:

#Error

...in the text box...Thanks for confirming my sanity with the logical solution but it keeps failing for some reason

any other thoughts??

 

[Crowley16]

ahh, but did you put "=" in front of it?

if you are putting a calculation forumla directly into a textbox, then I think you need =


--------------------
Procrastinate Now!

 

[cliffhanger9]

yes thsi is the formula i am using which produces the #Error message:

=Sum(IIf([txtDaysSinceLastLogin]<46,1,0))

 

[cliffhanger9]

as well as this...

=Sum(IIf((Now()-[KLIXLAST_LOGIN])<46,1,0))

as it is a calculated control rather then a bound one..

Thanks in advance for any tips!

 

[MichaelRed]

? perhaps I'm way out left, but a SIMPLE conditional query? Count of the field with the condition Field Relational operator to the value?







MichaelRed

 

[dhookom]

Make sure the name of the text box is not the name of a field:
[blue]=Sum( [/blue][green]Abs([txtDaysSinceLastLogin]<46)[/green] [blue])[/blue]
Also, this expression should be in a group or report Footer Or Header section. Not Page Sections.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cliffhanger9]

Now I have to enter parameters it is telling me!?

..why did i think this was goign to be something easy...

 

[dhookom]

What parameters are you being asked for? It is more than a bit difficult to see your screen.

I expect that txtDaysSinceLastLogin is probably a control name from your report's detail section. You can't sum a control. You can replace the control name with the control source property of the control name.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cliffhanger9]

Well, Duane, I appreciate your determination and patience

it jsut says enter parameter value as the title of the input box and then has the name of the control (txtDaysSinceLastLogin) and a text box to input a parameter...

This control resides in the Report Header as you said...

=Sum( Abs([txtDaysSinceLastLogin]<46) )
Also, this expression should be in a group or report Footer Or Header section. Not Page Sections.
Duane MS Access MVP

...but now you say it cant sum from the details section? I am confused.

just to clear this up...

i have a three controls (text boxes)

1 is in the details section bound to a table field called KLIXLAST_LOGIN - it is of date datatype

the second one is also in the details section and it is called txtDaysSinceLastLogin - the following code is in that control source:
=Now()-[KLIXLAST_LOGIN]
this successfully returns the number of days since the last login based on the date in the last login field

the final text box control is titled txtCountOk it resides in the report header as you suggested. The code in its control source is as follows:
=Sum(Abs([txtDaysSinceLastLogin]<46))

When i save it and then open it in preview view i get the above mentioned enter a parameter dialog.

Upon clicking cancel in this dialog it returns me to design view. upon clicking OK in this dialog i go to the report but the field in the report header is blank (the other two still work successfully)

Hope that is clear - god bless

 

[dhookom]

It's quite simple. txtDaysSinceLastLogin won't work in an aggregate function.
=Sum(Abs([txtDaysSinceLastLogin]<46))

As I stated "You can replace the control name with the control source property of the control name." This should work since I assume KLIXLAST_LOGIN is a field in your report's record source.
=Sum(Abs(Now()-[KLIXLAST_LOGIN]<46))




Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]