Dcount Unique Dates?, Access 2007 (1 Viewer)

skoolz · Aug 4, 2015

Hi All,

I want to do a unique count of dates when an activity was done in my table. The table may have multiple entries of the activity performed possibly on the same date by an individual

e.g. table entries

Code:

.

approvalNoSys   dateAssessed  Activity
100               01/08/2015    Audit
100               01/08/2015    Audit
100               01/05/2015    Audit
100               01/05/2015    Audit
100               01/02/2015    Audit
100               01/01/2015    Audit

.

Unique audit Count must equal 4

Code:

totV = ECount("[dateAssessed]", "R_P_Data_P", "[approvalNoSys] = '" & [Forms]![cmrOverview]![txtappNoSys] & "' AND [Activity] Like '*audit*'")

totV = Unique count

dateAssessed = date field in R_P_Data_P table
R_P_Data_P = table

"[approvalNoSys] = '" & [Forms]![cmrOverview]![txtappNoSys] & "' = criteria for the customer in question to separate them from many other customers in the table.

Activity = text field in R_P_Data_P table

audit = the activity

I'm also trying to avoid having to build total queries etc to them reference them, I'd like expert help in getting the desired outcome in an expression or small code.

As always, thanks in advance

PS - I read about Ecount but my complier doesn't recognise the function

plog · Aug 4, 2015

PS - I read about Ecount but my complier doesn't recognise the function

Nor do I, so I stopped comprehending when you started posting code.

I'm also trying to avoid having to build total queries etc to them reference them

I don't exactly follow that. You posted this in the Queries sub-forum, you want to Total your data. That means Totals Query to me.

So here's what I would do.

1. Create a subquery called 'ActivityDates_sub' to get the unique dates of each activity:

Code:

SELECT dateAssessed, Activity
FROM YourTableNameHere
GROUP BY dateAssessed, Activity;

2. Build another query on top of that to count all those unique Activities/Dates:

Code:

SELECT Activity, COUNT(dateAssessed) AS UniqueDays
FROM ActivityDates_sub
GROUP BY Activity;

ChrisTheIntern · Aug 4, 2015

I'm a beginner but I would try something like this:

Code:

Dim sqlStatement As String
 sqlStatement = "SELECT COUNT(*) As UniqueDates FROM (SELECT DISTINCT dateAssessed FROM R_P_Data_P WHERE Activity = 'Audit') AS tmp" 
 DoCmd.RunSQL sqlStatement

ChrisTheIntern · Aug 4, 2015

I just realized, RunSQL doesn't return anything.

What about...

Code:

Dim strSQL As String
Dim rs As DAO.RecordSet
Dim db As DAO.Database
Dim intCount As Integer

Set db = CurrentDb()
strSQL = "SELECT COUNT(*) As UniqueDates FROM (SELECT DISTINCT dateAssessed FROM R_P_Data_P WHERE Activity = 'Audit') AS tmp" 

Set rs = db.OpenRecordset(strSQL, dbOpenSnapShot)

intCount = rs("UniqueDates")

rs.Close
db.Close

ChrisTheIntern · Aug 4, 2015

By then, it would be simpler to DCount a temporary rs...

Code:

Dim strSQL As String
Dim rs As DAO.RecordSet
Dim db As DAO.Database
Dim intCount As Integer

Set db = CurrentDb()
strSQL = "SELECT DISTINCT dateAssessed FROM R_P_Data_P WHERE Activity = 'Audit'"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapShot)

intCount = DCount("dateAssessed", rs)

rs.Close
db.Close

Can you let me know if any of those works? I'm still learning.

skoolz · Aug 5, 2015

Thanks ChrisTheIntern for you assistance which is more in line with the type of solutions I was after....

I have tried your last suggestions and get a runtime type mismatch error pointing at the line below

intCount = DCount("dateAssessed", rs)

Hope you can help.

ChrisTheIntern · Aug 5, 2015

Glad I can give back some help to the forum. Replace the DCount with this:

intCount = rs.RecordCount

I think DCount only works for tables or queries that are concrete? Like in the navigation form, not a temporary one that is made within a sub.

Dcount Unique Dates?, Access 2007 (1 Viewer)

skoolz

Registered User.

plog

Banishment Pending

ChrisTheIntern

Registered User.

ChrisTheIntern

Registered User.

ChrisTheIntern

Registered User.

skoolz

Registered User.

ChrisTheIntern

Registered User.

Similar threads

Users who are viewing this thread