469,954 Members | 1,824 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,954 developers. It's quick & easy.

DCount Using Report Textbox as Criteria

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
3 4398
puppydogbuddy
1,923 Expert 1GB
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
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
1,923 Expert 1GB
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.

Similar topics

1 post views Thread by Simon Matthews | last post: by
1 post views Thread by Megan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.