By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,136 Members | 829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,136 IT Pros & Developers. It's quick & easy.

DCount Using Report Textbox as Criteria

P: 2
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?
Jun 22 '07 #1
Share this Question
Share on Google+
3 Replies

puppydogbuddy
Expert 100+
P: 1,923
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])
Jun 22 '07 #2

P: 2
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 :/
Jun 22 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
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.
Jun 22 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.