Dcount of a combobox

[Guest]

I have a field in a table that has information I have typed in a rowsource I
would like to do a dcount of the items in the combox in a report.

in the combobox row source I have Cancel, OnHold, Pending........

and I would like to count how many were Cancel, Oh hold and pending

 

[Guest]

You could use three separate DCount function calls or you can return all
three values with a single query. In the latter case you use the IIF
function to return 0 or 1 on the basis of the field's value and then SUM the
return values, so by summing all the ones and zeros you are in effect
counting all the ones. In a query it would go like this:

SELECT
SUM(IIF(MyField = "Cancel",1,0)) AS CountOfCancelled,
SUM(IIF(MyField = "OnHold",1,0)) AS CountOfOnHold,
SUM(IIF(MyField = "Pending",1,0)) AS CountOfPending
FROM MyTable;

Another way to get the same results would be to create a report based on the
table and group the report by the field in question, giving the group a Group
Footer in the report. The detail section of the report can be empty and zero
height. In the group footer you'd have a text box bound to the field and an
unbound text box with a ControlSource of:

=Count(*)

In a form you could have three unbound text boxes with ControlSource
properties such as:

=DCount("*","MyTable", "MyField = 'Cancel'")
=DCount("*","MyTable", "MyField = 'OnHold'")
=DCount("*","MyTable", "MyField = 'Pending'")