count function in continuous form

Posted on 2005-03-29
Medium Priority
Last Modified: 2006-11-18
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?
Question by:jonoscs
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 66

Expert Comment

by:Jim Horn
ID: 13659116
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.
LVL 27

Expert Comment

ID: 13659244
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

LVL 18

Accepted Solution

bonjour-aut earned 2000 total points
ID: 13659258
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

Author Comment

ID: 13667392
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.......
LVL 27

Expert Comment

ID: 13668679
could you please show us your dcount code you placed, and the name of the field you placed it on.


Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question