• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

count function in continuous form

i have a continuous for which i have created a roster system.....

i need at the bottom a count of all the feilds for that coloum where the contents of the feild are NOT "RDO", "OFF" or "PH" all the other feilds which are not the above 3  are the staff initials how do i do this, im stumped?
1 Solution
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Here's the workaround...

In your continous form, create another field, (I'll call it status) and in its .ControlSource type =iif([YourField] <> "RDO" AND [YourField] <> "OFF" AND [YourField] <> "PH", 1, 0).  Make it visible and display your form.  It should show this field with 1's for all values of YourField (yes, rename it to whatever your field is) and 0's for all other values.  

Then, create another control in your form footer section, and in its .ControlSource property type =Count([status])

Hope this helps.
Hi jonoscs,

Use DCOUNT or DSUM (as your question is not clear weather you want to Count How many fields you have OR want to Add the values of the fields)
So if you want to Count, make a textbox in the footer of the Form below the column, and in the Control Source of this textbox have this line:
=Dcount("*", "Table Name","[YourField]<>'RDO' and [YourField]<>'OFF' and [YourField]<>'PH'")

Same thing for the Dsum, just change the Dsum for Dcount.

Hope this helps

i recommend to do jimhorrns proposal already in the datasource of the continous form
if it is a query already just add a field with the switch function:

"SELECT ................., Switch(yourField="RDO",0,yourField="OFF",0,yourField="PH",0,1=1,1) As myCount  .........."

If you have a table as recorsource, you will need to change it to following query:
"SELECT *,  Switch(yourField="RDO",0,yourField="OFF",0,yourField="PH",0,1=1,1) As myCount FROM yourTablename;"

so this will be easy to maintain without changing the form

in the footer you build the sum: =Sum([myCount])

of corse there are further methods, but this will perform nicely as the usage of the switch fucncition is very performant.

Regards, Franz
jonoscsAuthor Commented:
yes i want to count the amount of records that are returned that don't have the field contents of OFF, RDO, PH
jjafferr i tried what you said, just got an #Error.......
could you please show us your dcount code you placed, and the name of the field you placed it on.


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now