Count Function

[kapzlok]

I want to write a function for a basic textbox in a form to display the amount of time a particular study appears in a table. I know I need the count function and an if statement but for some reason I get an error message when I try the following:

=COUNT(IIf(subject_study!study_name="Stroke","1","0"))

The subject_study table looks like the following:

patient_index study_name visit_id
1 Stroke 5012
2 Stroke 5013
3 Dyslexia 5014
4 Stroke 5015


....so basically I need to make a text box for each type of study, here there are only 2, "Stroke" and "Dyslexia" and I just need the textbox to display the total number, so for Dyslexia it should be 1 and for Stroke it should display "3". Any ideas what I'm doing wrong?

 

[PaulF]

Check out DCount()

PaulF

 

[THWatson]

In your report footer, you could also use
=Sum(Abs([study_name]="Stroke"))

Tom

 

[kapzlok]

I tried this first and then changed it to the second and still the same error message, I'm sure I'm entering the expression incorrectly, but I didnt find much help for dcount from Microsoft.

=DCount("[subject_study","study_name","study_name = 'Stroke'")

=DCount("[subject_study]![study_name]","Stroke")

 

[kapzlok]

=Sum(Abs([study_name]="Stroke"))

I tried that in the footer as well and received #error again.

 

[PaulF]

from the ACCESS 97 Help File

Syntax

DCount(expr, domain[, criteria])

The DCount function has the following arguments.

Argument Description
expr: An expression that identifies the field for which you want to count records. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.

domain: A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name.

criteria: An optional string expression used to restrict the range of data on which the DCount function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DCount function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise the DCount function returns a Null.

PaulF

 

[kapzlok]

from the ACCESS 97 Help File

Syntax

DCount(expr, domain[, criteria])

The DCount function has the following arguments.

Argument Description
expr: An expression that identifies the field for which you want to count records. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.

domain: A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name.

criteria: An optional string expression used to restrict the range of data on which the DCount function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DCount function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise the DCount function returns a Null.

PaulF"

I found that exact text and the example that they provide, however, they didn't provide the table from which the example was generated and so it was a bit difficult for me to understand what information I needed to place in each field.

I appreciate the help, you guys are fast!

 

[PaulF]

Try

DCount ("subject_study","study_name","study_name='Stroke'")

Where subject_study is the table or query name

study_name is a field in that table or query

and study_name is equal to "Stroke"

PaulF

 

[earthandfire]

Unless I've misunderstood, the table name is Subject_Study and the field name is Study_Name.

That being the case try

DCount("Study_Name", "Subject_Study", "Study_Name = 'Stroke'")

or

DSum("Study_Name", "Subject_Study", "Study_Name = 'Stroke'")

as appropriate.

 

[CosmoKramer]

kapzlok,

THWatson's earlier response should work:

In your [b][COLOR=red]report[/color][/b] footer, you could also use
=Sum(Abs([study_name]="Stroke"))

Are you sure you tried this in the report footer, and not in the page footer?? Calculated controls like this aren't allowed in the page footer.....



I'm CosmoKramer, and I approve this message.

 

[kapzlok]

DCount("Study_Name", "Subject_Study", "Study_Name = 'Stroke'")

That ended-up working perfectly. Thank you very much for the help.