DCount Using Report Textbox as Criteria

[Rebekkah]

I did a search but couldn't find a question similar to mine. I need to count the values in a textbox on a report and have the count subtotal in a group footer. But I need three different counts on the same textbox because there are three possible values and I need a count on each. I can't just do a simple DCount on the table and manually define the criteria because there are many different groups that will need the counts for only that group. Maybe I'm going about this all wrong and shouldn't use DCount at all. But here's what I've got so far:

=DCount("[Field1]","Table","[Field2] = 'Value1' AND [Field3] = '«Expr» [Textbox]' ")

[Textbox] is the field on the report that contains the value of the current group that is being subtotaled. But I get no results. I either get an #Error or a 0 (yes, there are matching values in the table).

Is there a way to use a report variable as criteria or is there a better way to calculate the counts?

[puppydogbuddy]

I did a search but couldn't find a question similar to mine. I need to count the values in a textbox on a report and have the count subtotal in a group footer. But I need three different counts on the same textbox because there are three possible values and I need a count on each. I can't just do a simple DCount on the table and manually define the criteria because there are many different groups that will need the counts for only that group. Maybe I'm going about this all wrong and shouldn't use DCount at all. But here's what I've got so far:

=DCount("[Field1]","Table","[Field2] = 'Value1' AND [Field3] = '«Expr» [Textbox]' ")

[Textbox] is the field on the report that contains the value of the current group that is being subtotaled. But I get no results. I either get an #Error or a 0 (yes, there are matching values in the table).

Is there a way to use a report variable as criteria or is there a better way to calculate the counts?

Not sure what you are trying to do, but try this:

=IIf(YourTextbox = Value1, Count([Field1]), IIf(YourTextbox = Value2, Count([Field2]), Count([Field3])))

The above assumes you have 3 columns, but only want the count of the column that is referenced in the textbox. Otherwise, if you want counts of all 3 columns, you need 3 textboxes in the footer, each with a count.

= Count([Field1])
= Count([Field2])
= Count([Field3])

[Rebekkah]

Not sure what you are trying to do, but try this:

=IIf(YourTextbox = Value1, Count([Field1]), IIf(YourTextbox = Value2, Count([Field2]), Count([Field3])))

The above assumes you have 3 columns, but only want the count of the column that is referenced in the textbox. Otherwise, if you want counts of all 3 columns, you need 3 textboxes in the footer, each with a count.

= Count([Field1])
= Count([Field2])
= Count([Field3])

I only have one column, that's the problem. I can do three columns but then I'd have to do a lot of extra coding. And if I used your formula, I'd have to change the report everytime a new value was entered for the textbox :/

[puppydogbuddy]

I only have one column, that's the problem. I can do three columns but then I'd have to do a lot of extra coding.

If you only have one column, but you want a count for each group, all you need to do is place a textbox in the <<<group footer>>> as follows:

= Count([YourField])

If you don't have a group footer, then invoke the sorting/grouping tool and select the field that you want to group on and provide the info requested in the dialog box. If you have a group footer that totals the Sum, all you have to do is place another textbox right below it with the above expression.