dcount and isnull

[Madmax]

Still learning a lot about expressions. Can someone tell me why this doesn't work



=Dcount([field name],[table name],isnull(n))

Basically I want to count how many records that have info in this field. The form and text box is both unbound and I set this as source. I am sure I am not using isnull correctly. What is the correct setup for isnull expression.

[Rod]

Two mistakes that I can see:

1. You must enclose the names in quotes. E.g. "[field name]"
2. The third optional parameter is not required. Here is the MS explanation where expr is "[field name]" in your situation.

The DCount function doesn't count records that contain Null values in the field referenced by expr unless expr is the asterisk (*) wildcard character. If you use an asterisk, the DCount function calculates the total number of records, including those that contain Null fields.

[boblarson]

It would be:

=Dcount("[field name]","[table name]","[SomeOtherFieldName] Is Null")

or if you wanted to count that [field name] where it was null then simply:

=Dcount("*","[table name]","[field name] Is Null")

[Madmax]

Thanks for the first DCount answer it worked but I am still having problems with this sheet and Dcount. Currently I have a field for security clearances needed that is a yes/No field. I have three test records in their one with a yes and two with a no. Below is the different ways I tried and the results. What am I missing please?

=Dcount("[Security Clearance Needed]","[Personnel Information]","yes") equaled 3

=Dcount("[Security Clearance Needed]","[Personnel Information]","True") equaled 3

=Dcount("[Security Clearance Needed]","[Personnel Information]","False") equaled 0

=Dcount("[Security Clearance Needed]","[Personnel Information]","1") Equaled 3

=Dcount("[Security Clearance Needed]","[Personnel Information]","0") Equaled 0

lol so what the ? Good news is this is the last DCount needed I think

[boblarson]

You need to examine the DCount that I gave you. You are nowhere near the syntax required with your examples you have tried.

See that we used the "*" for the first part, the table name and then the CRITERIA (which includes the field name and what it would be valued).

[Madmax]

yes and that applied to nulls, Which worked great but when it doesnt apply to nulls instead it applies to yes/no column is where I had the problem. Basically, the one that was for null worked great. Now this is a new version.
In your Formula, What is the * representing? the way I read it it is showing all the records that are is null. Should I be doing something like this instead?

=DCount("*","[Table Name]","[Field Name] =yes")


and if so, What is the * telling the DCount command so I know in the future. I am not only trying to write this particular database but learn completely on the parts of the formula for future reference.

[Madmax]

I just realized my mistake. in my book of notes I had it wrote down as DCount(field,Domain,Criterion) when it read DCount(Expression, Domain, Criterion) I fully understand my screw up on the expression sorry about miss matching that.

[boblarson]

By the way, the "*" is making it so we can count the number of records in the source table or query without having null values in a particular field affecting the count.