Access: Query Count

[apollo101]

I am trying to find a command that will allow me to be able to provide a count of the number of results returned from a query in Access. I can't, however, figure out what to place a counter on.
Thanks.

[RobDog888]

Being the database Guru that I am (sarcasism ) I believe if your passing or executing a SQL statement query you can add a aliased field with an agregrate function "Count"

SELECT Count(*) As TotalRecs, Field1, Field2, Field3 FROM Table1

[dannymking]

Being the database Guru that I am

Hmm

SELECT Count(*) As TotalRecs, Field1, Field2, Field3 FROM Table1

Access won't like this as it doesn't know what to do with field1 - field3

You either need to group the remaining fields, or just use the count..

SELECT Count(*) As TotalRecs, Field1, Field2, Field3 FROM Table1 GROUP BY Field1, Field2, Field3

or

SELECT Count(*) As TotalRecs FROM Table1

This first query will return a count per grouping, the second query will return a count of all records..

Now The stinger..

SELECT DCount("Field1","Table1") AS TotalRecs, Field1, Field2, Field3 FROM Table1

Will return a count of all records and each of the fields without the grouping..

Database Guru Indeed

[Ecniv]

Stay away from dcount - v slow....

well.. I guess you'd have to do some tests, but as the tables grow it slows rapidly (so I found).

Use it if you wish it is you who are the developer and it is your choice.

[dannymking]

Not one I would use normally, just an option...

It was a dig at robdog

[RobDog888]

I missed the GROUP BY.

Its been a tough week already with everybody picking on me.

[dannymking]

Just Jesting...

[RobDog888]

I know (smilie indicators ) Its hard being at the top you know.

[dannymking]

As I already quoted... Database Guru Indeed

[apollo101]

Hey, I appreciate all the responses, but I have still been unable to get this working. What I'm Trying to do is fill in a textbox with " X records returned".
The closest that I have been able to get is a count of the objects on the form!? Could you please provide a more detailed example - I am specifically having problems with the Count(*) parameters and confused because TotalRecs doesn't appear in the option list.
Thanks.

[RobDog888]

How are you executing this query and from where?

[apollo101]

I am executing the query from a form. By having a user press a search button once they have completed search fields of their choice. The results are then displayed on a subform which is contained in the main form. The query code behind the search button is as follows:

Me.subForm1.Form.Filter = "Field1 like '" & Me.txtfield1 & "' " & "and Field2 like '" & Me.txtfield2 & "' "
Me.subForm1.Form.FilterOn = True
Me.subForm1.Requery

[Besoup]

I am not sure of the exact code but you could probably get away with moving to the last record of the recordset and I think it is me.currentrecord that would return the number.

[RobDog888]

Yes, .CurrentRecord will get the recordcount if on the last record but will be inacurate if the recordset allows new rows to be added. If it does then the .CurrentRecord will off by one if its at the EOF.

The most reliable and accurate way is to use the .RecordCount property of the recordset that is bound to the form.

Dim rs As Recordset
    Set rs = Me.Recordset
    MsgBox rs.RecordCount

[dannymking]

Seeing as the recordset is opened in a cache.. even that may not return the complete results...

try

Dim rs As Recordset
  Set rs = Me.Recordset
  Do While Not rs.EOF
    rs.MoveLast
    rs.MoveNext
  Loop
  MsgBox rs.RecordCount