Counting Problem

[shewolf]

I've got a database that I've "inherited" that tracks various documents. Each document has it's own unique ID. However the database was set up such that rather than having subjects linked in a parent child table, all subject records are in the main table so I end up with something like:

DOCID SUBJECT_CODE
X24567 234
X24567 999
X24567 138

So what's happening when I try and do a count of the field DOCID, so I can determine exactly how many records we have for each geographic area, the system is counting document X24567 three times. So needless to say the count very quickly gets way, way off. I've tried setting the query properties to Unique Values and Unique Records and it doesn't seem to make a difference.

I found a piece of code by Allen Browne, http://allenbrowne.com/ser-66.html, that seems like it should work. It goes like this:

Public Function ECount(Expr As String, Domain As String, Optional Criteria As String, Optional bCountDistinct As Boolean) As Variant
On Error GoTo Err_Handler
'Purpose: Enhanced DCount() function, with the ability to count distinct.
'Return: Number of records. Null on error.
'Arguments: Expr = name of the field to count. Use square brackets if the name contains a space.
' Domain = name of the table or query.
' Criteria = any restrictions. Can omit.
' bCountDistinct = True to return the number of distinct values in the field. Omit for normal count.
'Notes: Nulls are excluded (whether distinct count or not.)
' Use "*" for Expr if you want to count the nulls too.
' You cannot use "*" if bCountDistinct is True.
'Examples: Number of customers who have a region: ECount("Region", "Customers")
' Number of customers who have no region: ECount("*", "Customers", "Region Is Null")
' Number of distinct regions: ECount("Region", "Customers", ,True)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String

'Initialize to return Null on error.
ECount = Null
Set db = DBEngine(0)(0)

If bCountDistinct Then
'Count distinct values.
If Expr <> "*" Then 'Cannot count distinct with the wildcard.
strSql = "SELECT " & Expr & " FROM " & Domain & " WHERE (" & Expr & " Is Not Null)"
If Criteria <> vbNullString Then
strSql = strSql & " AND (" & Criteria & ")"
End If
strSql = strSql & " GROUP BY " & Expr & ";"
Set rs = db.OpenRecordset(strSql)
If rs.RecordCount > 0& Then
rs.MoveLast
End If
ECount = rs.RecordCount 'Return the number of distinct records.
rs.Close
End If
Else
'Normal count.
strSql = "SELECT Count(" & Expr & ") AS TheCount FROM " & Domain
If Criteria <> vbNullString Then
strSql = strSql & " WHERE " & Criteria
End If
Set rs = db.OpenRecordset(strSql)
If rs.RecordCount > 0& Then
ECount = rs!TheCount 'Return the count.
End If
rs.Close
End If

Exit_Handler:
Set rs = Nothing
Set db = Nothing
Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "ECount Error " & Err.Number
Resume Exit_Handler
End Function

I've copied the code into a new module and have made sure that DAO is checked in references. However, when I try and build a field in a query using ECOUNT I get an "undefined function" error.

My query field statement is: TOTALS: ECOUNT("DOCID","TIC_HVUSER1",True)

I'm sure I'm just missing one tiny little thing that should be obvious but for the life of me I can't figure out what's going wrong.

Any and all assistance will be greatly appreciated.

 

[boblarson]

Did you put the function into a STANDARD module and not a form module?

 

[shewolf]

Not sure what the difference is? I clicked on the Modules tab in Access. Then said, "New" which took me to a VBA editor window where I then "pasted" the code verbatim.

Thanks,

C.

 

[boblarson]

Yep, that would be a standard module. After looking at the input of the function again, it would appear that you should have an extra comma:

TOTALS: ECOUNT("DOCID","TIC_HVUSER1",,True)

as the True (boolean) is the optional input at the end and the criteria, since you don't have any should just be blank with a comma.

 

[shewolf]

Hi thanks for the quick feedback. Unfortunately that didn't work either. When I cut and pasted your text and tried to run the query I got an error message.

The expression you entered contains invalid syntax. You may have entered a comma without a precding value or identifier.

Then if I remove the "red" comma it goes back to the "undefinded function 'ECOUNT' in expresion"



Should I perhaps give the module a different name? Could the fact that the module and the function within it have the same name be causing the system to be confused?

 

[shewolf]

Curiouser and curiouser...

Changed the name of the module so now the query will run but I realized it's giving the the total count of all documents in the collection rather than what I want which is:

Park Total
Jellystone 20
BigMountain 15
LittleMtn 10
and so on...

so I'm getting a result that looks like:
Park Total
Jellystone 9999
BigMtn 9999
LittleMtn 9999

 

[shewolf]

Still Can't Figure This one out

Anyone have any other ideas? I am totally stumped on this one and my boss really wants a custom report that includes that piece of data. Thanks!

 

[Brianwarnock]

Here is a straw to clutch at, in the example in the code
TOTALS: ECOUNT("DOCID","TIC_HVUSER1",,True)
would have a space between the commas

TOTALS: ECOUNT("DOCID","TIC_HVUSER1", ,True)

you have nothing to lose by trying it.

Brian

 

[Jon K]

...... I realized it's giving the total count of all documents in the collection rather than what I want ...

Since you are using the function in a correlated way with the [Park] field in the main query, you need to put in the Criteria, too.

TOTALS: ECOUNT("DOCID","TIC_HVUSER1", "[Park]='" & [Park] & "'", True)
.