Use and Expr in ECount VBA

[BLFOSTER]

I am using ECount with multiple criteria. All works well except this first criteria which is suppose to tell me how many students have 100% attendance: ECount("[Student ID]", "MyQuery", "[Expr2] >= 0.1 (2 other criteria follow). It just returns a count of student ID's within the other 2 parameters but skips over this one. I think I am writing the percent wrong or something. I also need to work with >=0.75, <=0.74 and that count is not working either. Any ideas?

[ssanfu]

Can't give a good response without seeing your calling expression. Care to post it?

[June7]

ECount is a custom function? Post the function code as well as the complete expression that calls the function and the formula that calcs Expr2. Should give the calculated field a better name.

[ssanfu]

ECount() is from Allen Browne's site... I think
http://www.allenbrowne.com/ser-66.html

[BLFOSTER]

It is Expr2: [SumOfTotal Hours]/[SumOfHrs Exp] to get the percent of attendance for the month.

[BLFOSTER]

This is Allen Brown's ECount. It works super. My calculation is in a query. Yeah, I should rename [Expr2]....here is what I have on Report Activiate Event:
Me.PerfectAttendance = ECount("[Student ID]", "GetAllAttendanceCountsQuery2", "[Expr2] >= 0.1 and [Benefits Branch] = '0701' And [Week of] Between #" & [Forms]![DHSParticipationPicker]![cbo_MonthBegin] & "# And #" & [Forms]![DHSParticipationPicker]![Cbo_MonthEnd] & "#", True)

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

[BLFOSTER]

Well, the problem is not with the ECount. It is with the totals query. I cannot type the date range into the query (for example 4-4-15 to 5-2-2015) because the management who will use this report will be looking at the data for different date ranges - hence the date pickers on the form. ECount will not work when the query has parameters such as: Forms!DhsParticipationPicker!cbo_MonthBegIn and it also will not work with parameters in the Query such as DateSerial or other date function. So the totals query is totaling all of the attendance rather than the date range I need. ECount is right, the data is wrong. Also, the organization that is asking for this report, analyze their data from Saturday to Saturday of the given month - so this month would be May 30 through July 3rd, next month would be July 4th through July 31st and August would be August 1(Saturday) through October 2nd and so on. ECount in VBA works with Date functions such as [MyDate] between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0) "), but that won't help me. Do you know how I would get the totals query to sum the month from Saturday to Saturday as explained above, without writing 12 queries so I can still use my ECount? Thanks for any help. Been stuck on this for days and am running out of time......

[June7]

So Saturday is the first day of the week and therefore the first day of a 'month' and month will not break within the week? Unusual.

Your 'August' is 2 months - Aug 1 to Oct 2, did you mean Aug 1 to Sep 4? Why not Aug 1 to Aug 28? May 30 to Jun26? What rules determine a 'month'?

Calculate a field in query that returns a custom month identifier and use that value to aggregate data. That 'identifier' could be a month number as revised for your unusual months or the date of first day of 'month'. I don't think either will be easy to dynamically calc.

A table of these 'months' might be easiest. Will need records input for every year. Then a DLookup() could pull the correct 'month' ID from this table.

This could eliminate need for the custom function and just use the intrinsic functionality of an aggregate query or report Grouping & Sorting with aggregate calcs.

[ssanfu]

It seems to me that you could modify the ECount() function..... you have the code!

Take the date criteria out of the criteria argument and add in two date arguments. Then modify the code to add the dates to the "WHERE" clause of the SQL..
The new call parameters would (could) look like:

Public Function ECount(Expr As String, Domain As String, Optional  Criteria As String, Optional dtStart as Date, Optional dtEnd as Date, Optional bCountDistinct As Boolean) As Variant

You would have to add code to ensure that both of the dates were entered in the form ([Forms]![DHSParticipationPicker]![cbo_MonthBegin]) and modify where the criteria for the SQL string (2 places).

How are your VBA skills?


Might be easier..... I'm just sayin...

[BLFOSTER]

My VBA skills are pretty basic. I decided to get the unique records by using the totals query and then use DSum and DCount as control sources for the text boxes on the report. Works great! Everything is adding up as it should. Now, I built these reports while I had the database offline. The database is split. When I put it back online, none of my DSum or DCount functions are working! Any idea why that would be? Thank you so much

[June7]

What do you mean by 'offline' - the backend was on your local computer drive? 'Online' means backend on network server? If the frontend can find the backend tables, everything including code and calcs should work. No idea why the functions are not working. Exactly what is happening - error message, wrong results, nothing?

[BLFOSTER]

We have a split database - back-end is on SQL Server and shared across a Network. I am rebuilding the front end, so I converted the tables in my copy of the front-end to local tables to avoid dealing with the Network while I am working.

I don't get ANY results now that I am connected to the back end. Just #error# where the results used to be. It has something to do with the Totals query but it makes no sense to me ..... Thank you

[June7]

Try rebuilding the query.

[BLFOSTER]

It worked!! Thank you!!!

I am sure curious as to what and why that happened though....do you know?

[June7]

Possibly corruption. Something in Access system support tables was messed up. Deleting and/or rebuilding object cleans up the corruption. Will never know exactly what was corrupted.