Dataset problem - Too few parameters

[Les Isaacs]

Hi All

In a module I need to open a dataset that includes 2 subqueries. I created the main query - with the 2 subqueries - using the querybuilder, and this all ran perfectly, so I then went to sql view to get the sql, and pasted this into the OpenRecordset statement. The initial query had one field ([practice]) that needed a parameter from the current form, and I knew I had to replace the reference to the current form with a variable from the sub - so:

[Forms]![frm x main]![prac name]
...became...
'" & strPractice & "'
(strPractice is also used elsewhere in the sub)

Having done that, I'm still getting the "Too Few Parameters..." error :banghead:

The complete OpenRecordset statement is:

    Set rsStaffNormals = CurrentDb.OpenRecordset("SELECT [qry x all staff].practice, [qry x all staff].staff_name, " & _
    "(SELECT sum([amount]) from [qry x main table next mth] " & _
    "where [qry x main table next mth].[staff_name]=[qry x all staff].staff_name) " & _
    "AS TotalAmount, " & _
    "(SELECT sum([qry x main table next mth].[stafpay].[Tnumber]) " & _
    "from [qry x main table next mth] " & _
    "where [qry x main table next mth].[staff_name]=[qry x all staff].staff_name) " & _
    "AS TotalNumber, [qry x all staff].hasLEFT " & _
    "from [qry x all staff] " & _
    "WHERE [qry x all staff].practice= '" & strPractice & "' " & _
    "AND [qry x all staff].hasLEFT=False;")

Can anyone see what's up with this
Grateful for any help.
Les

 

[gemma-the-husky]

you often get this issue with resolving form references.

I think the correct procedure is to wrap the parameter in eval function to avoid this - but i generally replace the form reference with a function to read the variable - which works well for me.

so you may get there by replacing strpractice with a function readpractice()

function readpractice as string
readpractice = strpractice
end function

 

[Les Isaacs]

Hi Dave

Thanks (again!) for your help.
Unfortunately replacing strpractice with a function readpractice() didn't work - still getting "Too few parameters"

The replacement of

with
'" & strPractice & "'
generally works - eleswhere I have

140 Set rsStaffNormals = CurrentDb.OpenRecordset("SELECT staffs.staff_name, Sum(Nz([amount],0)) AS TotalAmount, Sum(Nz([Tnumber],0)) AS TotalNumber " & _
"FROM staffs LEFT JOIN stafpay ON staffs.staff_name = stafpay.staff_name " & _
"GROUP BY staffs.staff_name, stafpay.[month name], staffs.practice, staffs.hasLEFT " & _
"HAVING (stafpay.[month name]= '" & PayRunDate & "' Or isnull(stafpay.[month name])) AND staffs.[Practice] = '" & strPractice & "' AND Staffs.[Hasleft] = False " & _
"ORDER BY Staffs.staff_name;")

which works fine (but gives the wrong results - hence the need for the new dataset)

I think the problem may be to do with the fact that the dataset is taking data from [qry x main table next mth], which is itself taking parameters from [frm x main]. I can't see why this should be, however - surely [qry x main table next mth] would be accepted just like a table?

Thnaks for any further suggestions.
Les

 

[Pat Hartman]

In the original query you posted, the subqueries didn't have group by clauses so they wouldn't work. In general, you shouldn't use subqueries when you can accomplish the same action with a join to a saved querydef since Jet/ACE don't optomize subselects well.

Also, the problem may be with where the criteria is being applied. Totals queries can use both WHERE and HAVING clauses as required. Just understand that the WHERE clause is applied BEFORE aggregation and the HAVING clause is applied AFTER. So, the WHERE clause would be used for criteria that is aggregated away and the HAVING would be used for columns that are returned in the final recordset. For example, if you wanted to sum the sales for departments for the month of march and return only the departments with sales in excess of $1,000, you would use the WHERE clause to select only March data and you would use the HAVING to select only sales > $1,000.

 

[DrallocD]

It would be much easier to read if you alias your tables.

I would guess that the problem is with the following in your second subquery:

[qry x main table next mth].[stafpay].[Tnumber]

 

[Les Isaacs]

Hi Pat

Many thanks for coming in on this ... but now I'm confused

When you say

the subqueries didn't have group by clauses so they wouldn't work

This definitely works fine as a query, and gives the expected results:

SELECT [qry x all staff].practice, [qry x all staff].staff_name, (SELECT sum([amount]) from [qry x main table next mth]
where [qry x main table next mth].[staff_name]=[qry x all staff].staff_name) AS TotalAmount, (SELECT sum([qry x main table next mth].[stafpay].[number])
from [qry x main table next mth]
where [qry x main table next mth].[staff_name]=[qry x all staff].staff_name) AS TotalNumber, [qry x all staff].hasLEFT
FROM [qry x all staff]
WHERE ((([qry x all staff].practice)=[forms]![frm x main]![prac name]) AND (([qry x all staff].hasLEFT)=False));

I've been quite pleased with myself recently for getting to grips with subqueries ... but perhaps a little knowledge is a dangerous thing

I really don't know what else to try here, so would be grateful for any further input.
Thanks again
Les

 

[Pat Hartman]

I see now that the subqueries are only selecting the aggregate and no other field so the group by isn't necessary (trying to sort out all the spaces, parens, and brackets makes my eyes cross. The queries will be easier to read if you get rid of the spaces because that will allow you to get rid of the square brackets.) but that doesn't change the statement that this could be slower than the join method.

 

[DrallocD]

I would still like to know how you can have :

[qry x main table next mth].[stafpay].[Tnumber]

as a field in a subquery. Am I missing something?

 

[Les Isaacs]

Hi Pat

I appreciate what you're saying about the spaces etc. My excuse is that I inherited this accdb, and that correcting the naming convention is a long job (which I'm tacking steadily)
I will try to do this with a joined query to see if it workes that way

Hi 'DrallocD'

I'm not sure what the problem is with
[qry x main table next mth].[stafpay].[Tnumber]
... it seems to work fine in the query

Thanks again
Les

 

[DrallocD]

Hi Pat

I'm not sure what the problem is with
[qry x main table next mth].[stafpay].[Tnumber]
... it seems to work fine in the query

Les

It is probably just another hole in my VBA knowledge. I know that [qry x main table next mth] is the query and I assume that Tnumber is a field but what is stafpay?

I didn't realize how many holes there were until I discovered this site!

What do you get if you run the following as a query (you will need to supply a value for Q.practice in the WHERE:

SELECT  Q.practice, 
 Q.staff_name, 
 (SELECT sum([amount]) 
  from [qry x main table next mth] AS QM
  where QM.[staff_name]=Q.staff_name) AS TotalAmount, 
 (SELECT sum(QM.[stafpay].[Tnumber]) 
  from [qry x main table next mth] AS QM
  where QM.[staff_name]=Q.staff_name) AS TotalNumber, 
 Q.hasLEFT 
from [qry x all staff] AS Q
WHERE Q.practice= '' 
AND Q.hasLEFT=False