Using DCOUNT in Access with multiple criteria

Posted on 2012-09-10
Last Modified: 2012-09-10

I have a database that tracks pupil progress with different subjects which are rated at different levels . I have a Y/N filed which indicates if the subject has been passed.  I have this written in the footer of the form

=DCount("PupilID","S2 List","PupilID=" & [PupilID] & " AND Pass= TRUE" AND " LCode=3 ")

If I run it with

=DCount("PupilID","S2 List","PupilID=" & [PupilID] & " AND Pass= TRUE" )

It tells me how many subjects have been passed but if I add the bit that asks  for those subjects with level (LCode) 3 that have been passed it just counts all the records my guess is i have some quotes in wrong place but cannot work it out ...

Any help appreciated.
Question by:tmckeating
    LVL 61

    Accepted Solution

    Try this:

    =DCount("PupilID","S2 List","PupilID=" & [PupilID] & " AND Pass= TRUE AND  LCode=3 ")

    Author Closing Comment

    Thanks you saved me hours of torture with spaces quotes and perfectly! Much appreciated.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now