problem with DCOUNT 1

[dharkangel]

Hi,

I am trying to execute this query in Access but am not getting the results I want. The field 'Assign To' contains User names like this: Smith, John. Issue ID is just that, an ID column that can never be blank which is why I am counting it. I am trying to query a table and get all distinct Usernames along with the Count of Issues that are overdue, which is why you see me using the Commit Date to compare against today's date. I was trying to use the new variable called 'User' that I create in this query as part of my DCOUNT criteria but Access does not let me probably due to incorrect syntax. Can anyone tell me what I am doing wrong?
++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SELECT DISTINCT (BarcelonaInternalActions.[Assign To]) AS User, DCOUNT("[Issue ID]","BarcelonaInternalActions","[Commit Date]< Now) AND BarcelonaInternalActions.[Assign To]=User") AS myCount
FROM BarcelonaInternalActions;

 

[PHV]

and what about this ?
SELECT [Assign To] AS User, Count([Issue ID]) AS myCount
FROM BarcelonaInternalActions
WHERE [Commit Date] < Now()
GROUP BY [Assign To];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dharkangel]

Thank you once again, PHV. This works great.