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

If you're going to do that, make sure you include
"rst.movelast" before "intRecordCount = rst.RecordCount"

<quote>
The RecordCount property doesn't indicate how many records are contained in a 
dynaset-, snapshot-, or forward-only-type Recordset object until all records have 
been accessed. Once the last record has been accessed, the RecordCount property 
indicates the total number of undeleted records in the Recordset or TableDef object. 
To force the last record to be accessed, use the MoveLast method on the Recordset 
object.
</quote>



On 1 May 2008 at 13:00, Max Wanadoo wrote:

> 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