Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Carol W.

  2. #2
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    [quote name='WSC' post='792613' date='08-Sep-2009 19:07']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.[/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

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='kwvh' post='792618' date='08-Sep-2009 18:15']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[/quote]

    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.
    Carol W.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Carol W.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •