UtterAccess Discussion Forums > Retrieve Total Number of Records from Query

Full Version: Retrieve Total Number of Records from Query

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

jkunkler

Nov 2 2008, 03:00 PM

Hi All,

I have a table that I created a query over using Group to eliminate duplicates, but what I really need is the total number of records that the query produces. I tried using SQL with Distinct and Count and I just can't seem to get it.

Any help would be greatly appreciated.

truittb

Nov 2 2008, 03:05 PM

Do you want the number of records that were aggregated with the count function or the number of records that the query actually returns?

jkunkler

Nov 2 2008, 03:09 PM

I just need the number of records that the query returns. I need this number to perform a calculation. Also, how can I do this using SQL?

gary84

Nov 2 2008, 03:23 PM

Pass the name of the query object, or the SQL string, to this function.

CODE

Function QueryRecordCount(strQuery As String) As Long
    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    Set db = CurrentDb()
    Set rst = db.OpenRecordset(strQuery, dbOpenDynaset)

    QueryRecordCount = 0
    If Not rst.EOF Then
        rst.MoveLast
        QueryRecordCount = rst.RecordCount
    End If

    rst.Close
    db.Close
End Function

vtd

Nov 2 2008, 04:44 PM

If you already have the saved Query, you can create another Query / SQL with the SQL String:

CODE

SELECT Count(*) FROM [YourSavedQuery]

to get a Query that returns the Count.

If you don't use the first Query for anything else, perhaps, it is better to include the Count in this Query instead of a set of 2 Queries.

Check Access Help on the DCount() function also...

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.