Running Access Query Returns Incorrect Number of Records

[Tim]

I am running some code which loops through and runs the queries in an
Access Database. The count of the records is then stored in a
Worksheet.

For most of the queries, the figure stored in the Worksheet matches
the number of records if you run the query in Access. But for certain
queries, the count of the records does not match the number of records
when the query is run in Access.

If I change the query to a make table query and then base another
query on that table, the results are correct.

Can anybody suggest running the query from Excel would results in a
different number of records being returned than running the query in
Access ?

The code is as follows:

Function CountTheRecords(ByVal strQueryName As String, OpenConnection
As ADODB.Connection) As String

Dim OConn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

On Error GoTo Error_Handler

strSQL = "SELECT Count(" & strQueryName & ".[Patient NHS No]) AS
[CountOfPatient NHS No] " & _
"FROM " & strQueryName & ";"

Set rs = New ADODB.Recordset

With rs
.Open strSQL, OpenConnection, adOpenStatic

CountTheRecords = rs![CountOfPatient NHS No]

.Close
End With

Set rs = Nothing

Exit Function

End Function