Using Isnull with Dcount

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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"
 
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"
Click to expand...


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.
 
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")
 
Thank you both for your assistance. The following syntax did the job.
Result = DCount("*", "tblIssuePermits", " [MergeLetter] =-1")



fredg said:
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"
Click to expand...


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.
Click to expand...
 
Back
Top