counting yes/no fields in a table

Posted on 2003-03-07
Medium Priority
Last Modified: 2006-11-17
Hi, I need to count the number of times a field in 'table1' is true for a specific form 'id' (from table2).
This value needs to be stored as an integer.
Any ideas?
I have tried both of these but they return errors.
where table1 = ukcShapeData and table2 = ResearchInToDCD.

'Forms!ResearchIntoDCD![NoOfShapesPassed] = DCount("[ShapePassed] ", "[UKCShapeData]", "[SubjectNum] = Forms!ResearchIntoDCD![subject number]"And [UKCShapeData]![ShapePassed]=Yes)

Forms!ResearchIntoDCD![NoOfShapesPassed] = IIf(ShapePassed = Yes, DCount("*", "[UKCShapeData]", "[SubjectNum] = Forms!ResearchIntoDCD![subject number]"), 1)
Question by:simonpgill
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
  • 2
  • 2

Accepted Solution

phL earned 300 total points
ID: 8088601
You may have been close with your first attempt -

'Forms!ResearchIntoDCD![NoOfShapesPassed] = DCount("[ShapePassed] ", "[UKCShapeData]", "[SubjectNum] = Forms!ResearchIntoDCD![subject number]"And [UKCShapeData]![ShapePassed]=Yes)

However, the syntax needs work.  Try -

Forms!ResearchIntoDCD![NoOfShapesPassed] = DCount("[ShapePassed] ", "[UKCShapeData]", "[SubjectNum] = " & Forms!ResearchIntoDCD![subject number] & " And [UKCShapeData]![ShapePassed] = Yes")


Author Comment

ID: 8088609
The problem has been solved. The solution is

Forms!ResearchIntoDCD![NoOfShapesPassed] = DCount("[ShapePassed] ", "[UKCShapeData]", "[SubjectNum] = Forms!ResearchIntoDCD![subject number] And [UKCShapeData]![ShapePassed]=Yes")

If anyone else tries this.
This code works.

Author Comment

ID: 8088621
cheers Phill we just worked it out also but you can have the points anyway.

Expert Comment

ID: 8088650
Spaces and Quotes and building strings always give me problems until I have my AM coffee.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

777 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