[AccessD] A2003: Distinct or Group by in a DLookup

Darren.  This will work, but it is a bit of a work-around:

Public Function pfCountClientIDs()
  Dim qdf As QueryDef
  On Error Resume Next 'if it already exists then don't worry about it.
  Set qdf = CurrentDb.CreateQueryDef("Q_CountClientIDs", "SELECT DISTINCT
ClientID FROM tblPendingActions")
  pfCountClientIDs = DCount("ClientID", "Q_CountClientIDs")
  set qdf=nothing
End Function

Max
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Thursday, May 01, 2008 1:00 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] A2003: Distinct or Group by in a DLookup

Darren.
The only way I could get this to work (without a query to work directly on)
is this:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT ClientID FROM tblPendingActions
GROUP BY tblPendingActions.ClientID;") intRecordCount = rst.RecordCount

Max
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren D
Sent: Thursday, May 01, 2008 5:11 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] A2003: Distinct or Group by in a DLookup

Hi Max

No it didn't - it still returned the number of records in the table It
should work though - not sure why it doesn't Love the concept I ended up
setting up a DAO recordset object to do this I am going to play with your
suggestion a while more though Darren
-----------------
T: 1300 301 731

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Thursday, 1 May 2008 3:42 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] A2003: Distinct or Group by in a DLookup

Darren:
Did this work?
Max
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Wednesday, April 30, 2008 7:46 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] A2003: Distinct or Group by in a DLookup

You could try something like this:

intRecordCount = DCount("[ClientID]", "tblPendingActions","ClientID in
(select ClientID from tblPendingActions group by ClientID)") 

This is Air Code and needs testing, but should work.  It will be slow on
large datasets.

Max

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Wednesday, April 30, 2008 7:02 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] A2003: Distinct or Group by in a DLookup

Not AFAIK.  DCount() only operates on a Table or Query.  
One way would be to create and save the  query "Select Distinct ClientID
from 
tblPendingActions".   Then use DCount on that saved query.



On 30 Apr 2008 at 15:30, Darren D wrote:

> Hi Team
> 
> I have a simple DLOOKUP working well
> 
> intRecordCount = DCount("[ClientID]", "tblPendingActions")
> 
> But I want to know - Is there a "Distinct" version of the same?
> 
>  
> 
> EG
> 
> intRecordCount May return 10 records (but this may only be across say
> 2
> accounts)
> 
> So I want to know if there is something where I can group on the 
> ClientID - like
> 
> intRecordCount = DCount(Distinct("[ClientID]", "tblPendingActions"))
> 
>  
> 
> Many thanks in advance
> 
>  
> 
> Darren
> 
>  
> 
>  
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com