Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2008
    Posts
    163

    Unanswered: proper syntax to nz dcount two criterias

    One of my tables is a many-to-many named SectStd (it has sectionIDs and studentIDs). The field studentIDs is a foreign key from the StudentStatus table. StudentStatus table has a field named "Details" which contains one of six possible alphanumerical sentences.

    I would like a textbox in my form to show the count of studentIDs with a certain Detail when a combobox with SectionCode is chosen:

    In the afterupdate of my combobox:

    Nz(DCount("[studentID]", "[StudentStatus]", "[Detail] like 'Enrolled'" AND "[SectStd].[studentID] = me.sectCBox"), 0)
    Which doesn't work, what's wrong in the syntax?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you are asking for studentID = the literal text "me.SectCBox"

    rather than the content of the textbox SectCBox on the Me form.

    also, it is unorthodox to use Like without a wildcard

    izy


    LATER:
    no, it's much MUCH worse than that
    your criteria-as written is a logical AND of two strings.
    "somestring" AND "otherstring"
    i have no idea how Dxxx() will respond to that, but it certainly wont give you the result you want.

    try something like:
    "fieldA like '*blah*' and fieldB = " & me.someBox


    Last edited by izyrider; 09-11-08 at 03:46.
    currently using SS 2008R2

  3. #3
    Join Date
    Jun 2008
    Posts
    163
    I'm getting a type mismatch error...

    Nz(DCount("[studentID]", "[StudentStatus]", "[Detail] = 'Enrolled'" AND "[SectStd].[studentID] = [me].[sectCBox]"), 0)

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    yup
    see the LATER bit in previous post.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jun 2008
    Posts
    163
    Hmm, I can't translate into code the following:

    Of the records with "[Detail] = 'Enrolled'" in my StudentStatus table, count the number of studentIDs whose matching SID in the SectStd table has a sectionCODE that matches the sectCBox in my form

    Hmm, that actually sounds like three. Will "where [studentstatus].[dir_SID] = [Sectstd].[SID]" work?


    also, I found a wrong fieldname in my syntax (it didn't solve the error though)

    "[SectStd].[sectionCODE] = [me].[sectCBox]"
    I moved on from type mismatch to "syntax error"

    Nz(DCount("[studentID]", "[StudentStatus]", "[Detail] = '*Enrolled*'" AND "[SectStd].[studentID] = "& [me].[sectCBox] &" "), 0)


    hmm.. on a side note, is there a way to just count the number of records that matches 1 criteria that appears in a subform each time a record in the main form is selected?
    Last edited by coffeecat; 09-11-08 at 04:12.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Maybe:

    Nz(DCount("[studentID]", "[StudentStatus]", "[Detail] = '*Enrolled*' AND [studentID] = " & Me.[sectCBox]), 0)

    Forgive me if this doesn't work, I've got the 'flu and I'm not firing on all thrusters.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry 'Trekker, that won't work under 99.999999% of the cases. it would only work if field Detail was the bizarre literal string "*ENROLLED*"

    last hint from me: try using a string var to build the criteria: debug.print the string var and keep working at it until it makes sense.

    this is sounding more and more like homework - i'm out.


    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Why are you using NZ() at all? DCount should return 0 automagically if the count is in fact 0.

    Much like SELECT COUNT(*), it's not going to be NULL if no records are found.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Jun 2008
    Posts
    163
    Why are you using NZ() at all? DCount should return 0 automagically if the count is in fact 0
    No particular reason (since I'm unfamiliar with the difference between dcount and nz dcount -I've heard about the return 0 but I've no idea what even that means). Anyway, I've been trying to use dcount previously for another part of my db but hadn't had luck using it, when I asked the forums about it, I was given an nz line of code and I've been using it since.

    try using a string var to build the criteria: debug.print the string var
    Now this is the point where I wish I was asking about homework because it would mean that I'm educated in the right school to know what Izy meant. =D

    Now, I'm trying to figure out how to just count the records that appears in a subform every time it is refreshed. I'll post again to tell you guys how that goes.

    My main problem with my first approach is figuring out how to put into code that the two criteria are based on fields in two separate tables. One table identifies which student ID belongs to which section Code, and the second table identifies the enrollment status of each student ID. I know that "AND" is used for multiple criteria, but being able to tell access that there's two tables is beyond me (I think this is what Izy's "string var" meant).

    Thanks! =D
    Last edited by coffeecat; 09-11-08 at 22:12.

  10. #10
    Join Date
    Jun 2008
    Posts
    163
    I was able to fix it. =D

    My first mistake was that I kept the "AND" outside the quotation marks.

    To fix the two-tables thing, I created a simple query (yes, using wizard) that has the sectionCode, studentID, Details and a couple of other relevant fields. I then used Dcount based on the query instead of two separate tables. The query records however, are pretty huge.

    On an odd note, in my criteria, I noticed that the code does not work when I use [queryFieldname] = [formObjectname] (I receive a type mismatch error). It does however, recognize [formObjectname] = [queryFieldname].

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ehhh... are you trying to reference the value of a control on a form from within your query?

    If so, you have to fully qualify the reference so Access knows where to look:

    forms!yourFormName!yourControlName
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    As long as it works
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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