Unable to count instances

[BrentJ]

I have a list box (status box) based on a query (daily status query) with fields Rank, Last Name, First Name, MI, MOS, Status, Remarks (the list box is bound to Last Name). Status is a combo box with 6 possible values. I need to count the instances of each value and display each count at the bottom of the form. I have tried every possible way I can think to write the expression, using count, dcount, counting in a seperate query and then sum on the column but it always comes up #error. I have also tried using count on the table directly. I have even tried putting a subform in the form based on the query and using count with the subform.

=Count([Soldier Data subform1].Form!Status="Present For Duty")

I'm not real familiar with count so perhaps I'm not using the proper format for the expression. Thanks for any help.

 

[PHV]

And what about a continuous subform with RowSource set to something like this:
SELECT Status, Count(*) As Total FROM yourTable GROUP BY Status;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BrentJ]

That didn't work for me but I did get it to work by entering an expression into a blank field in the query (Just found out you could do that).

(SELECT Count(Status)FROM [Soldier Data] WHERE Status = "Present For Duty")

Thanks for your help.