Select Distinct from Query

Topic

New Reply

I have a query called qry-OA_ this_ month’s_ hours and would like to show the total number of distinct full names in a report footer. I have a field called full (which is a concatenation of [Last Name] & [First Name]). Here is my code:

Dim dbsmember As Database
Set db = CurrentDb
Dim rst As Recordset

Dim inttotmems
Set rst = CurrentDb.OpenRecordset(“SELECT distinct [qry-OA_ this_ month’s_ hours].full FROM [qry-OA_ this_ month’s_ hours]”)
inttotmems = rst.RecordCount
Text33 = inttotmems

I am getting a runtime error 3061 “Too few parameters. Expected 2” message. What am I doing wrong?

Thanks, in advance.

Reply | Quote

Replies

I have a query called qry-OA_ this_ month’s_ hours and would like to show the total number of distinct full names in a report footer. I have a field called full (which is a concatenation of [Last Name] & [First Name]). Here is my code:

Dim dbsmember As Database
Set db = CurrentDb
Dim rst As Recordset

Dim inttotmems
Set rst = CurrentDb.OpenRecordset(“SELECT distinct [qry-OA_ this_ month’s_ hours].full FROM [qry-OA_ this_ month’s_ hours]”)
inttotmems = rst.RecordCount
Text33 = inttotmems

I am getting a runtime error 3061 “Too few parameters. Expected 2” message. What am I doing wrong?

Thanks, in advance.

Does the “qry-OA_ this_ month’s_ hours” query have criteria? If you are passing criteria to the query via a form, you will get that error. You may need to copy the SQL code (from SQL View) and use it as your recordset source.

Ken

Reply | Quote

Does the “qry-OA_ this_ month’s_ hours” query have criteria? If you are passing criteria to the query via a form, you will get that error. You may need to copy the SQL code (from SQL View) and use it as your recordset source.

Ken

Yes, the query has criteria.

Following your suggestion (I think), I tried the following statement and got the same error but with “… Expected 4”:

Set rst = CurrentDb.OpenRecordset(“SELECT distinct [Last Name] & [First Name] AS [full],[tblOrgActionTransactions-hours].[Last Name], [tblOrgActionTransactions-hours].[First Name]”)

Here is the full SQL copied from SQL view of the query:

PARAMETERS [Month to print] Text ( 255 ), [Year to print] Short;
SELECT [Last Name] & [First Name] AS [full], [tblOrgActionTransactions-hours].[Last Name], [tblOrgActionTransactions-hours].[First Name], [tblOrgActionTransactions-hours].[Enter month], [tblOrgActionTransactions-hours].[Enter year], [tblOrgActionTransactions-hours].[Enter activity], [tblOrgActionTransactions-hours].[Enter hours for month]
FROM [tblOrgActionTransactions-hours]
WHERE ((([tblOrgActionTransactions-hours].[Enter month])=[Month to print]) AND (([tblOrgActionTransactions-hours].[Enter year])=[Year to print]))
ORDER BY [tblOrgActionTransactions-hours].[Last Name], [tblOrgActionTransactions-hours].[First Name], [tblOrgActionTransactions-hours].[Enter activity];

What am I missing? Thanks.

Reply | Quote

Opening Recordsets based on parameter queries can be difficult. I think it would be easier to avoid opening a recordset.

Save “SELECT distinct [qry-OA_ this_ month’s_ hours].full FROM [qry-OA_ this_ month’s_ hours]” as a query – say qryDistinctNames

then inttotmems = dcount(“*”,”qryDistinctNames”) would provide the number without needing a recordset.

Reply | Quote

John,

Thanks for the reply.

In implementing your suggested solution, the prompt for the two parameters came up. This was not desirable in the formatting / printing of the report footer.

Just to close the loop on this issue, I ended up saving all the full names in a temporary table (tblTempNames) in the Detail On Print event. Then, in the ReportFooter On Print event, I used the following statements:

Set rst = CurrentDb.OpenRecordset(“SELECT DISTINCT [Full Name] FROM tblTempNames”)
inttotmems = rst.RecordCount

This worked perfectly.

Thanks to everyone on this board I’ve learned something about using criteria queries as the basis for a recordset.

Reply | Quote