Using Isnull with Dcount

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

iholder - 19 May 2006 18:32 GMT

I need to count no a nulls on a field call [MergeLetter]. This field is a
boolean.

I tried this syntax. It returns a zero value if it is null or if it is not
null.

Result = DCount("*", "tblIssuePermits", " Isnull([MergeLetter])")

I am testing if the user made a selection. If a selection is made it
processes the Mail Merge. If not an Error Message " Must make selection"

Reply to this Message

fredg - 19 May 2006 18:59 GMT

> I need to count no a nulls on a field call [MergeLetter]. This field is a
> boolean.
[quoted text clipped - 6 lines]
> I am testing if the user made a selection. If a selection is made it
> processes the Mail Merge. If not an Error Message " Must make selection"

A Boolean field value is either -1 or 0, not Null.
If [[MergeLetter]] is Boolean (-1 or 0) Then

Result = DCount("*", "tblIssuePermits", " [MergeLetter] = 0")
or
Result = DCount("*", "tblIssuePermits", " [MergeLetter] =-1")
will return a count.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

iholder - 19 May 2006 20:04 GMT

Thank you both for your assistance. The following syntax did the job.
Result = DCount("*", "tblIssuePermits", " [MergeLetter] =-1")

> > I need to count no a nulls on a field call [MergeLetter]. This field is a
> > boolean.
[quoted text clipped - 14 lines]
> Result = DCount("*", "tblIssuePermits", " [MergeLetter] =-1")
> will return a count.

Reply to this Message

Douglas J Steele - 19 May 2006 19:00 GMT

While it's a VBA function, DCount (and the other domain aggregate functions)
expects valid SQL as the Where portion.

Try:

Result = DCount("*", "tblIssuePermits", "[MergeLetter] Is Null")

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Reply to this Message