Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    South Bend, IN (but Texas is home!)

    Unanswered: DCount in Calculated field

    I've been searching through the archives and while I see that folks try to avoid using these kinds of calculated field, I'm afraid that my boss pretty much insists on viewing his data this way.

    What I'm trying for:
    A form with calculated fields which count: the total # of visits we've had, how many men vs. women (this is a combobox based on a value list in the originating table), how many ESL students (this is a yes/no field), and several other details along the same lines.

    I have a filter which asks for the semester for which my boss needs these numbers and that does successfully give the total number of visits we've had.

    The problem:
    How do I use calculated fields to give me the breakdown of these other details? An example of the yes/no field I tried is this:
    =DCount("[ESL]","[ESL Query]","[ESL] = 0")
    but I'm not getting correct results.
    (Right now I'm getting results of 0; in some other permutation that I don't remember I was getting a result of 1. I should have 3 if it's NOT still using the filter correctly or 2 if it is behaving exactly right.)

    I'm guessing that either my query is messed up -- but it works fine on its own -- or that I've yet again not fully undestood the syntax for what I'm trying to do.

  2. #2
    Join Date
    May 2003
    Provided Answers: 5
    =DCount("[ESL]","[ESL Query]","[ESL] = 0")

    you are counting records based on a fix number from the query, if that query has no criteria then the dcount wil never change unless of course you add or delete records.

    when you apply a filter you are at that point "drilling down" further into the recoedset and subsequently making a new subset of the original recordset and then requerying the form based on the subset

    but the dcount will still run against the query object, you may need to count against the new subset of the form


    or create a second query that uses certain fields on the form for its underlying criteria (this is the simplest way)
    and then just do dcount of the query to count the rows with no other criteria

    dim x as variant
    x = dcount("[myfield]","mysecondquery")
    Last edited by axsprog; 06-13-03 at 16:49.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts