Count problem

[jax]

I have posted something similar to this before so sorry if I am repeating myself.
I have an Option Group on a subform containing two options whose results are stored in the table as 1 and 2. I have a Dcount calculation in the footer of the subform counting the 1's and 2's, but I am getting confused with the Dcount function. I can get the Dcount to count all the 1's and 2's in the table but not just the ones relating a particular building. I have tried several options two of which are below:

=DCount("[BuildingID]","tblARoom"," [Purpose]='1'")

=DCount("[BuildingID]","tblARoom","[BuildingID] = " & "me.[Purpose]='1'")

The first one doesnt give the results I need. The second option doesnt work at all. Can anyone help me?

 

[Dembrey]

If the field Purpose is not a text field try:

DCount("[BuildingID]","tblARoom"," [Purpose]=1")

[i.e. remove the single quotes from around the 1].

For the second line the check value of [Building ID] appears to be missing, try something like:

=DCount("[BuildingID]","tblARoom","[BuildingID]=2 AND [Purpose]=1").

I've edited this OnTheFly so I hope it is correct (i.e. it's not tested). Hope it helps anyway.


[This message has been edited by Dembrey (edited 03-25-2002).]

 

[Rich]

Why can't you just "Count" the records on the subform?

 

[jax]

The Count function just returns #Name! I tried that first before getting bogged down with DCount because it seemed such a simple thing to want to do. I basically want to count the number of 'occupied rooms' in a building. For count I entered:

=Count([Option1])

Option1 is the name of the 'occupied' option button in an option group.

 

[Rich]

Assuming [Purpose]is the field that's storing the opt value, if so then Sum(Iif([Purpose]=1,1,0))in the subform footer should work.
HTH

 

[jax]

If I try the Sum(Iif) suggestion I get an error message and strangely the other sum fields in the subform footer, which all worked perfectly, all give error messages too. They are unrelated to the 'purpose' field. It seems very strange. Is the Sum/count/dcount problem because its an option group?