Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2003
    Posts
    76

    Unanswered: IsNull & NZ not working...

    Hello all!

    The following query:
    Code:
    SELECT [Lease Assignment].Employee, Count(NZ(([Lease Assignment].[Tax_check_or_filing_fee]),0)) AS Tax_check_or_filing_fee
    FROM [Lease Assignment]
    WHERE ((([Lease Assignment].Date) Between [Forms]![Menu]![txtLA_PRfrom] And [Forms]![Menu]![txtLA_PRto]))
    GROUP BY [Lease Assignment].Employee, [Lease Assignment].Tax_check_or_filing_fee
    HAVING ((([Lease Assignment].Tax_check_or_filing_fee)=0));
    Does not work correctly! I'm trying to count the number of unchecked checkboxes in a table... but if an employee has no unchecked checkboxes, they simply dont show up in this query.

    Shouldn't the NZ function take care of this, so that even though they don't have any unchecked boxes, the employee name and "Tax_check_or_filing_fee" will contain 0, while being included in the query?

    I've tried every combo of IIF(IsNull & NZ there is... could anyone offer some advice?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    SELECT COUNT(IIf(MyYesNoFieldHere=False,1,0)) FROM MyTableNameHere;

  3. #3
    Join Date
    Jun 2003
    Posts
    76
    I still get the same result... The employee that has 0 checkboxes checked is not showing up.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by Sucoyant
    I still get the same result... The employee that has 0 checkboxes checked is not showing up.
    Let's see your "new" query ... It might be something either in your GROUP BY or your HAVING clauses ...

    AH HA! Check your HAVING clause ... Your test is for 0. Try changing that to False ...

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    maybe also worry about a default value for the checkbox (=default value for the field in the table): default=FALSE should behave as described above.

    as far as i recall, no default value sets the checkbox (...table field) = NULL until the user ticks/unticks ...but then it's a long time since i left Yes/No fields without a default.

    if you forgot a default and have buckets of data with NULL values, you should set a default in the table def and then fix existing records with an update query (find the NULLs and update to FALSE)

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Jun 2003
    Posts
    76
    _this_ is getting irritating.

    I've tried it all! I clicked and unclicked the boxes, just to see what would happen, and still nothing. I've tried 0, and i've tried Flase.

    Anyone else have any suggestions?

    I really appreciate everyones time.

  7. #7
    Join Date
    Jun 2003
    Posts
    76
    I have a similar query:

    Code:
    SELECT [Lease Assignment].Employee, IIf(IsNull(Count([Lease Assignment].[Tax_check_or_filing_fee])),False,Count([Lease Assignment].[Tax_check_or_filing_fee])) AS Title_sent_correct_party, [Lease Assignment].Tax_check_or_filing_fee
    FROM [Lease Assignment]
    WHERE ((([Lease Assignment].Date) Between [Forms]![Menu]![txtLA_PRfrom] And [Forms]![Menu]![txtLA_PRto]))
    GROUP BY [Lease Assignment].Employee
    HAVING (((Count([Lease Assignment].Tax_check_or_filing_fee))>=0) AND (([Lease Assignment].Tax_check_or_filing_fee)=0));
    That works.. but it doesn't include the Employee field.
    It seems that the Employee field is the thing thats making the query flip out... because in this query, if nothing is checked, it will return a 0, unlike the other query.

  8. #8
    Join Date
    Jun 2003
    Posts
    76
    When the query runs, it should look like this:

    Employee_Name Tax_chk_or_filing_fee
    -------------- ----------------------
    John Doe 0
    John Smith 8


    Instead it looks like this:

    Employee_Name Tax_chk_or_filing_fee
    -------------- ----------------------
    John Smith 8


    John doe is left out for some reason. Even though the count should evaluate to 0, and look like the first one... it doesn't.

    Any ideas? I appreciate everyones help!

  9. #9
    Join Date
    Jun 2003
    Posts
    76
    Table:
    Attached Thumbnails Attached Thumbnails table.jpg  

  10. #10
    Join Date
    Jun 2003
    Posts
    76
    This is what the result looks like.

    See, it only shows people that have unchecked boxes... I need it so that it shows everyone.
    Attached Thumbnails Attached Thumbnails result.jpg  

  11. #11
    Join Date
    Jun 2003
    Posts
    76
    Getting closer.. i took out the HAVING clause...

    I just need to figure out how to get rid of the middle item, and keep the 2 items that the arrow is pointing to.
    Attached Thumbnails Attached Thumbnails closer.jpg  
    Last edited by Sucoyant; 01-02-04 at 17:56.

  12. #12
    Join Date
    Jun 2003
    Posts
    76
    Nevermind. It's fixed.

    Here is what the query now looks like:

    Code:
    SELECT [Lease Assignment].Employee, Sum(IIf([Lease Assignment].Tax_check_or_filing_fee,0,1)) AS Tax_check_or_filing_fee
    FROM [Lease Assignment]
    WHERE ((([Lease Assignment].Date) Between Forms!Menu!txtLA_PRfrom And Forms!Menu!txtLA_PRto))
    GROUP BY [Lease Assignment].Employee;

Posting Permissions

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