Les Isaacs
Registered User.
- Local time
- Today, 11:41
- Joined
- May 6, 2008
- Messages
- 186
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:
Can anyone see what's up with this
Grateful for any help.
Les
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:
Code:
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