Understanding Dcount (1 Viewer)

[reglarh]

I have a form that displays statistics on a table using Dcount, including looking at missing data.

On a general point, why is the first parameter in the Dcount function required? Naming the table/query and the selection conditions is surely all that is needed?

I have created a number of counts successfully, but when I make a format error in the next one all of the previously correct ones now give errors, even when I delete the offending entry. I cannot understand why.

Finally, I wish to count the number of members who do not have an email address. The table is imported from an external system and the field is null if empty. There seems no way of doing this. Am I wrong?

 

[ebs17]

why is the first parameter in the Dcount function required?

* ... Records are counted
Fieldname ... Contents of the field that are not NULL are counted

 

[Gasman]

On a general point, why is the first parameter in the Dcount function required? Naming the table/query and the selection conditions is surely all that is needed?

Why not look at the syntax? https://support.microsoft.com/en-gb/office/dcount-function-c1fc7b93-fb0d-4d8d-97db-8d5f076eaeb1

Description​

Counts the cells that contain numbers in a field (column) of records in a list or database that match conditions that you specify.

The field argument is optional. If field is omitted, DCOUNT counts all records in the database that match the criteria.

No idea what you mean with your third paragraph.

Yes. Have you tried 'Is Null' ?

 

[June7]

The first argument is required for all domain aggregate functions. However, with DCount don't have to specify a field, can use * wildcard.

DCount("*", "table") will count ALL records

DCount("*", "table", "field Is Null") will count only records with Null in field

@Gasman, that link is for Excel. With Access DCount, criteria argument is not required.

 

[Gasman]

@Gasman, that link is for Excel. With Access DCount, criteria argument is not required.

Oops, my bad. I have always just used the *

 

[cheekybuddha]

Am I wrong?

I suspect so! Maybe not!

But, remember, we can't see your screen so we can't be sure what you have tried.

I have created a number of counts successfully, but when I make a format error in the next one all of the previously correct ones now give errors, even when I delete the offending entry. I cannot understand why.

Eg What is the 'next one'? What are the 'previously correct ones'?

Perhaps post some examples of things you have tried, and whether they worked or didn't work

 

[June7]

I also do not understand what is happening in your second paragraph of OP.
Domain aggregate functions should rarely be needed on report.
Could provide db for analysis.

 

[The_Doc_Man]

I have a form that displays statistics on a table using Dcount, including looking at missing data.

On a general point, why is the first parameter in the Dcount function required? Naming the table/query and the selection conditions is surely all that is needed?

I have created a number of counts successfully, but when I make a format error in the next one all of the previously correct ones now give errors, even when I delete the offending entry. I cannot understand why.

Finally, I wish to count the number of members who do not have an email address. The table is imported from an external system and the field is null if empty. There seems no way of doing this. Am I wrong?

According to the link below, you DO need the first argument but it can be an asterisk, meaning "wild card". The article says it even counts null records when using "*" as the argument.

I am unable to tell you WHY that first argument is required because Access code is NOT OpenSource. We cannot see what they actually do with that argument. However, if I am correct in my guess, they use it to build a dynamic SQL string. Leaving the argument empty would mess up their string building operation.

DCount Function - Microsoft Support

support.microsoft.com

The error situation you described that occurs after a format error requires us to see the code for a full diagnosis. However, if you are feeding back something into a commonly used criteria argument and suddenly you get a null or impossible syntax, that would do it. Another thing to consider is that if you are using a continuous form, there is actually only one set of "data feeders" so an error in one instantly becomes an error in all.

Counting entries that do not have an e-mail address can be handled by using something like:

x = DCount( "*", "mytable", "IsNull([EmailAddr])")

There are other ways to do this but this would be simplest, perhaps.

 

[June7]

IsNull() is a VBA function call. IS NULL is SQL and preferred in SQL structure. It works in the DCount so Doc's suggestion that code is constructing SQL makes sense.

Side Note: IsNull() is also a function in T-SQL (used by SQLServer) but serves same purpose as Nz() does in Access.

 

[Pat Hartman]

@June7 Just to be complete

DCount("SomeColumnName", "SomeTableOrQueryName")

Will count all rows where SomeColumnName is NOT Null. This is the syntax that confuses everyone. They frequently think it will count the unique values of SomeColumnName but it doesn't.

Oops, my bad. I have always just used the *

The asterisk is the most efficient option. It allows the function to use database statistics. Otherwise, unless there is an index on the column name, the technique is RBAR (Row By Agonizing Row) because each row has to be analyzed for a null value so it can be ignored.