Count records with condition...

[A.Farsinejad]

Does anybody know how can I count the records of a table based on a condition
and put the value in a field?

I have two table which are related to each other.
Table 1: Problems (Fields: problem number, number of cases,...)
Table 2: Cases (Fields: case number, number in problems, ...)

Form1roblems (all the fields are from the table "Problems") problem
number, number of cases, ...
in this form the "number of cases" should be the number of cases in "Cases"
table which the "number in problems" is equal to "problem number".

I use the expressions for the "number of cases" in Form1, but with all kind
of expressions or even queries the result in"#Name?"...
Exp: IIF(Cases.[number in problems]=Problems.[problem
number],Count(cases.[case number]),0) ....

could you help me on this issue?

 

[Ken Snell [MVP]]

Use an expression similar to this:

=DCount("*", "Cases", "[Number in Problems]=" & [problem number])

The above expression assumes
-- [problem number] is a numeric data type field, not a text
datatype field
-- your Form1 has the [problem number] field in its Record Source
dataset

If [problem number] is a text datatype field, then use this expression:

=DCount("*", "Cases", "[Number in Problems]='" & [problem number] & "'")