dcount and is not null conflict?

[Paintballlovr]

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?

[RuralGuy]

You might try Not Is Null.

[Paintballlovr]

You might try Not Is Null.

I just tried your idea, but still no luck.

[RuralGuy]

How about posting your full DCount() string?

[Paintballlovr]

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"

[Paintballlovr]

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?

[RuralGuy]

I guess we'll need to see the SQL for your query [qrySELECT followup Worklist A-F]

[Missinglinq]

I believe that DCount, unlike DLookUp, when finding no match, returns a Zero, not a Null.

Does this help???

Linq ;0)>

[Paintballlovr]

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.

[Paintballlovr]

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

[Paintballlovr]

Thank you to everyone who looked at this, but by the nature of the project being changed, this is no longer an issue.