Results 1 to 11 of 11
  1. #1
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96

    dcount and is not null conflict?

    Is using a "is not null" criteria not possible, when I want to do a dcount of the results of a query? I have form A and query A, which my dcount statement works just fine. form B is a copy of A and query B is a copy of A, except for one criteria has been changed from "is null" to "is not null". On form B, I get no results. No error, no 0, nothing just an blank space where the number should be. If I point form B to query A, it works fine, so I think the issue has to be with query B. Any ideas or am I out of luck?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,921
    You might try Not Is Null.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96
    Quote Originally Posted by RuralGuy View Post
    You might try Not Is Null.
    I just tried your idea, but still no luck.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,921
    How about posting your full DCount() string?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96
    Quote Originally Posted by RuralGuy View Post
    How about posting your full DCount() string?
    =DCount("*","qrySELECT followup Worklist A-F") . This string works fine on the form, if the form itself is changed to look at query A "qrySELECT Worklist A-F"

  6. #6
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96
    I hope I haven't wasted your time. Could the issue be with trying to use the "is not null" on a date field? When I go in and modify data so I know the query should have results, then the number pops up on the form. I assumed I would get 0 on the form if there were no records, like the other forms, but maybe that's where the is not null acts differently?

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,921
    I guess we'll need to see the SQL for your query [qrySELECT followup Worklist A-F]
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  8. #8
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,998
    I believe that DCount, unlike DLookUp, when finding no match, returns a Zero, not a Null.

    Does this help???

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96
    Quote Originally Posted by RuralGuy View Post
    I guess we'll need to see the SQL for your query [qrySELECT followup Worklist A-F]
    dcount database.accdb

    I've attached a database with the files in question. Form Worklist A-F is fully functional, showing records in the worklist and a count below the form name. The form is pointed to source qrySELECT worklist a-f and dcount is pointed to source qrySELECT worklist a-f.

    Form Followup Worklist A-F is fully functional**, but only because the form is pointed to source qrySELECT worklist a-f, instead of qrySELECT Followup worklist a-f. dcount is pointed to the correct source qrySELECT followup worklist a-f (showing 0 count). If I point the form to the correct source of qrySELECT followup worklist a-f, I lose the count of 0 and the field just goes blank. Maybe I just didn't realize it and this is expected behavior, but I didn't think the form itself would have influence over the dcount field.

  10. #10
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96
    Anyone who is looking at this, hold off for the time being. There are most likely some query changes coming, which might affect this issue. Let me get those in place and if it's still broken, I'll update the thread that the issue is still outstanding. Thanks everyone

  11. #11
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96
    Thank you to everyone who looked at this, but by the nature of the project being changed, this is no longer an issue.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Write Conflict Help
    By jbailey4545 in forum Access
    Replies: 5
    Last Post: 01-24-2014, 02:25 PM
  2. Write conflict
    By IslandHydro in forum Programming
    Replies: 4
    Last Post: 10-25-2013, 09:30 AM
  3. Write Conflict
    By sah in forum Forms
    Replies: 8
    Last Post: 04-25-2012, 07:19 AM
  4. another conflict problem..
    By mann2x in forum Access
    Replies: 6
    Last Post: 10-17-2010, 07:14 AM
  5. Query Conflict
    By isnpms in forum Queries
    Replies: 3
    Last Post: 08-21-2010, 12:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums