[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