Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple Dcount (2003 SP1)

    First time of trying this Function & I haven't got it quite right. I want to count all the non-nulls within a field, in a grouped query.

    The table is [TblMerge] & the field is [CmCAT].

    The expression I used is; CauNumber: DCount("HPCAT","TblMerge","HPCAT Is Not Null")

    But the result I get is a count of ALL records in the table.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple Dcount (2003 SP1)

    The expression in itself is correct, so there are two possibilities:
    - You meant "CmCAT Is Not Null" (you mentioned CmCAT as field name, but used HPCat in the expression)
    - The field doesn't contain null values, but for example an empty string "" or a space " ". You could test
    <code>
    "HPCAT <> """""
    </code>
    as where-condition (quotes within a quoted string must be doubled).

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Dcount (2003 SP1)

    Hmm, that didn't help. Here's a stripped down DB.

    PS I've now 2 fields which need non-nulls counting.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple Dcount (2003 SP1)

    You must include the query criteria in the where-condition:
    <code>
    CnmNumber: DCount("*","TblMerge","CmCAT Is Not Null And Press = '09' And Status = 'O'")

    CauNumber: DCount("*","TblMerge","HPCAT Is Not Null And Press ='09' And Status = 'O'")</code>

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Dcount (2003 SP1)

    Can't seem to get this working. I just put one of the lines in.
    The error message reads;

    "Syntax error (missing operator) in query expression 'CauNumber: DCount("*","TblMerge","HPCAT Is Not Null And Press ='09' And Status = 'O'") '.

    This is the SQL;

    SELECT TblMerge.Press, TblMerge.Status, Count(TblMerge.HPCAT) AS CountOfHPCAT, Count(TblMerge.CmCAT) AS CountOfCmCAT
    FROM TblMerge
    WHERE CauNumber: DCount("*","TblMerge","HPCAT Is Not Null And Press ='09' And Status = 'O'")
    GROUP BY TblMerge.Press, TblMerge.Status
    HAVING (((TblMerge.Press)="09") AND ((TblMerge.Status)="O"));

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple Dcount (2003 SP1)

    That is not valid SQL. The expressions I posted were meant to be used in design view, not in SQL view. Here is the modified SQL for the query in the database you posted:

    SELECT TblMerge.Press, TblMerge.Status, Count(TblMerge.HPCAT) AS CountOfHPCAT, TblMerge.CmCAT, DCount("*","TblMerge","CmCAT Is not Null And Press = '09' And Status = 'O'") AS CnmNumber, DCount("*","TblMerge","HPCAT Is Not Null And Press ='09' And Status = 'O'") AS CauNumber
    FROM TblMerge
    GROUP BY TblMerge.Press, TblMerge.Status, TblMerge.CmCAT
    HAVING (((TblMerge.Press)="09") AND ((TblMerge.Status)="O"));

  7. #7
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Dcount (2003 SP1)

    Ho hum.

    Ah! The CnmNumber calculation is still counting nulls. Or alt least they seem to be nulls.

    Looking at un-grouped records, show these as blenk. They don't appear to be spaces.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple Dcount (2003 SP1)

    They are empty strings "", as a simple test proves (a query on the table with Is Null as criteria for CMCAT returns nothing, a query with "" as criteria returns lots of records). So the expression should be
    <code>
    DCount("*","TblMerge","CmCAT <> '' And Press = '09' And Status = 'O'")</code>

  9. #9
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Dcount (2003 SP1)

    Sorry to be a pain, but I was using the criteria 'Press = 09' for result checking. What I'm hoping to do in the final query is to have the 'press' field grouped without criteria.

    I can't get the changes to provide appropriate results for each grouped records.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple Dcount (2003 SP1)

    This?
    <code>
    SELECT TblMerge.Press, TblMerge.Status, Count(TblMerge.HPCAT) AS CountOfHPCAT, TblMerge.CmCAT, Val(DCount("*","TblMerge","CmCAT<> '' And Press = '" & [Press] & "' And Status = '" & [Status] & "'")) AS CnmNumber, Val(DCount("*","TblMerge","HPCAT Is Not Null And Press = '" & [Press] & "' And Status = '" & [Status] & "'")) AS CauNumber
    FROM TblMerge
    GROUP BY TblMerge.Press, TblMerge.Status, TblMerge.CmCAT;</code>

  11. #11
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Dcount (2003 SP1)

    Almost. I only gave half of the story. 'Status' still needs to be 'O'.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple Dcount (2003 SP1)

    It's hard if you keep shifting the goal. You can add that condition back in, though.

  13. #13
    New Lounger
    Join Date
    Dec 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Dcount (2003 SP1)

    Apologies Hans, I'm really struggling to see the logic of this functions syntax.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple Dcount (2003 SP1)

    Does it work now?

  15. #15
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Dcount (2003 SP1)

    Sorry for not getting back to you yesterday (finished work [img]/forums/images/smilies/smile.gif[/img] ).

    Anyway, I tried this (amongst others); CauNumber: DCount("*","TblMerge","HPCAT <> ''and Status = 'O'") & this returned the total count for all records.

    I'm afraid I'm not going to understand this function.

Page 1 of 2 12 LastLast

Posting Permissions

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