sql error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I keep getting "too few parameters" error, does anyon know why.

Set rst = db.OpenRecordset("SELECT Count(ALLPolicies.PolicyNo) " & _
"AS CountOfPolicyNo, Sum(ALLPolicies.AnnualPremium) " & _
"AS SumOfAnnualPremium, tblTeamLeaders.TeamID " & _
"FROM ALLPolicies INNER JOIN (tblTeamLeaders INNER JOIN tblEmployees " & _
"ON tblTeamLeaders.TeamID = tblEmployees.TeamID) " & _
"ON ALLPolicies.Agent = tblEmployees.UserId " & _
"WHERE (ALLPolicies.BoundDate) = Date() " & _
"GROUP BY tblTeamLeaders.TeamID " & _
"HAVING
(((tblTeamLeaders.TeamID)=[Forms]![frmTeamProductionStats]![TeamID]));")
 
Since you are not using a saved query, Access never has a chance to convert
the control reference to a value.

Assumption:
TeamID is a number field. If TeamID is a text field then you will need to
add string delimiters to the query.

Set rst = db.OpenRecordset("SELECT Count(ALLPolicies.PolicyNo) " & _
"AS CountOfPolicyNo, Sum(ALLPolicies.AnnualPremium) " & _
"AS SumOfAnnualPremium, tblTeamLeaders.TeamID " & _
"FROM ALLPolicies INNER JOIN (tblTeamLeaders INNER JOIN tblEmployees " &
_
"ON tblTeamLeaders.TeamID = tblEmployees.TeamID) " & _
"ON ALLPolicies.Agent = tblEmployees.UserId " & _
"WHERE (ALLPolicies.BoundDate) = Date() " & _
"GROUP BY tblTeamLeaders.TeamID " & _
"HAVING tblTeamLeaders.TeamID= " &
[Forms]![frmTeamProductionStats]![TeamID]

Change the last line to the following if TeamID is a string
"HAVING tblTeamLeaders.TeamID= """ &
[Forms]![frmTeamProductionStats]![TeamID] & """"

For better performance I would include the criteria in the HAVING Clause in
the where clause.

Set rst = db.OpenRecordset("SELECT Count(ALLPolicies.PolicyNo) " & _
"AS CountOfPolicyNo, Sum(ALLPolicies.AnnualPremium) " & _
"AS SumOfAnnualPremium, tblTeamLeaders.TeamID " & _
"FROM ALLPolicies INNER JOIN (tblTeamLeaders INNER JOIN tblEmployees " &
_
"ON tblTeamLeaders.TeamID = tblEmployees.TeamID) " & _
"ON ALLPolicies.Agent = tblEmployees.UserId " & _
"WHERE (ALLPolicies.BoundDate) = Date() " & _
"AND tblTeamLeaders.TeamID= " &
[Forms]![frmTeamProductionStats]![TeamID] & " " _
"GROUP BY tblTeamLeaders.TeamID "
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks for answering John, I switched to using the GroupBy like you suggested
and I am still getting a "too few parameters" message and in debug, it is
pointing to the line the following line

"GROUP BY tblTeamLeaders.TeamID;")
--
Leslie


John Spencer said:
Since you are not using a saved query, Access never has a chance to convert
the control reference to a value.

Assumption:
TeamID is a number field. If TeamID is a text field then you will need to
add string delimiters to the query.

Set rst = db.OpenRecordset("SELECT Count(ALLPolicies.PolicyNo) " & _
"AS CountOfPolicyNo, Sum(ALLPolicies.AnnualPremium) " & _
"AS SumOfAnnualPremium, tblTeamLeaders.TeamID " & _
"FROM ALLPolicies INNER JOIN (tblTeamLeaders INNER JOIN tblEmployees " &
_
"ON tblTeamLeaders.TeamID = tblEmployees.TeamID) " & _
"ON ALLPolicies.Agent = tblEmployees.UserId " & _
"WHERE (ALLPolicies.BoundDate) = Date() " & _
"GROUP BY tblTeamLeaders.TeamID " & _
"HAVING tblTeamLeaders.TeamID= " &
[Forms]![frmTeamProductionStats]![TeamID]

Change the last line to the following if TeamID is a string
"HAVING tblTeamLeaders.TeamID= """ &
[Forms]![frmTeamProductionStats]![TeamID] & """"

For better performance I would include the criteria in the HAVING Clause in
the where clause.

Set rst = db.OpenRecordset("SELECT Count(ALLPolicies.PolicyNo) " & _
"AS CountOfPolicyNo, Sum(ALLPolicies.AnnualPremium) " & _
"AS SumOfAnnualPremium, tblTeamLeaders.TeamID " & _
"FROM ALLPolicies INNER JOIN (tblTeamLeaders INNER JOIN tblEmployees " &
_
"ON tblTeamLeaders.TeamID = tblEmployees.TeamID) " & _
"ON ALLPolicies.Agent = tblEmployees.UserId " & _
"WHERE (ALLPolicies.BoundDate) = Date() " & _
"AND tblTeamLeaders.TeamID= " &
[Forms]![frmTeamProductionStats]![TeamID] & " " _
"GROUP BY tblTeamLeaders.TeamID "
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Leslie said:
I keep getting "too few parameters" error, does anyon know why.

Set rst = db.OpenRecordset("SELECT Count(ALLPolicies.PolicyNo) " & _
"AS CountOfPolicyNo, Sum(ALLPolicies.AnnualPremium) " & _
"AS SumOfAnnualPremium, tblTeamLeaders.TeamID " & _
"FROM ALLPolicies INNER JOIN (tblTeamLeaders INNER JOIN tblEmployees "
& _
"ON tblTeamLeaders.TeamID = tblEmployees.TeamID) " & _
"ON ALLPolicies.Agent = tblEmployees.UserId " & _
"WHERE (ALLPolicies.BoundDate) = Date() " & _
"GROUP BY tblTeamLeaders.TeamID " & _
"HAVING
(((tblTeamLeaders.TeamID)=[Forms]![frmTeamProductionStats]![TeamID]));")
Click to expand...
Click to expand...
 
I left off a line continuation

Set rst = db.OpenRecordset("SELECT Count(ALLPolicies.PolicyNo) " & _
"AS CountOfPolicyNo, Sum(ALLPolicies.AnnualPremium) " & _
"AS SumOfAnnualPremium, tblTeamLeaders.TeamID " & _
"FROM ALLPolicies INNER JOIN (tblTeamLeaders INNER JOIN tblEmployees " &
_
"ON tblTeamLeaders.TeamID = tblEmployees.TeamID) " & _
"ON ALLPolicies.Agent = tblEmployees.UserId " & _
"WHERE (ALLPolicies.BoundDate) = Date() " & _
"AND tblTeamLeaders.TeamID= " & _
[Forms]![frmTeamProductionStats]![TeamID] & " " & _
"GROUP BY tblTeamLeaders.TeamID "

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Leslie said:
Thanks for answering John, I switched to using the GroupBy like you
suggested
and I am still getting a "too few parameters" message and in debug, it is
pointing to the line the following line

"GROUP BY tblTeamLeaders.TeamID;")
--
Leslie


John Spencer said:
Since you are not using a saved query, Access never has a chance to
convert
the control reference to a value.

Assumption:
TeamID is a number field. If TeamID is a text field then you will need
to
add string delimiters to the query.

Set rst = db.OpenRecordset("SELECT Count(ALLPolicies.PolicyNo) " & _
"AS CountOfPolicyNo, Sum(ALLPolicies.AnnualPremium) " & _
"AS SumOfAnnualPremium, tblTeamLeaders.TeamID " & _
"FROM ALLPolicies INNER JOIN (tblTeamLeaders INNER JOIN tblEmployees
" &
_
"ON tblTeamLeaders.TeamID = tblEmployees.TeamID) " & _
"ON ALLPolicies.Agent = tblEmployees.UserId " & _
"WHERE (ALLPolicies.BoundDate) = Date() " & _
"GROUP BY tblTeamLeaders.TeamID " & _
"HAVING tblTeamLeaders.TeamID= " &
[Forms]![frmTeamProductionStats]![TeamID]

Change the last line to the following if TeamID is a string
"HAVING tblTeamLeaders.TeamID= """ &
[Forms]![frmTeamProductionStats]![TeamID] & """"

For better performance I would include the criteria in the HAVING Clause
in
the where clause.

Set rst = db.OpenRecordset("SELECT Count(ALLPolicies.PolicyNo) " & _
"AS CountOfPolicyNo, Sum(ALLPolicies.AnnualPremium) " & _
"AS SumOfAnnualPremium, tblTeamLeaders.TeamID " & _
"FROM ALLPolicies INNER JOIN (tblTeamLeaders INNER JOIN tblEmployees
" &
_
"ON tblTeamLeaders.TeamID = tblEmployees.TeamID) " & _
"ON ALLPolicies.Agent = tblEmployees.UserId " & _
"WHERE (ALLPolicies.BoundDate) = Date() " & _
"AND tblTeamLeaders.TeamID= " &
[Forms]![frmTeamProductionStats]![TeamID] & " " _
"GROUP BY tblTeamLeaders.TeamID "
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Leslie said:
I keep getting "too few parameters" error, does anyon know why.

Set rst = db.OpenRecordset("SELECT Count(ALLPolicies.PolicyNo) " & _
"AS CountOfPolicyNo, Sum(ALLPolicies.AnnualPremium) " & _
"AS SumOfAnnualPremium, tblTeamLeaders.TeamID " & _
"FROM ALLPolicies INNER JOIN (tblTeamLeaders INNER JOIN tblEmployees
"
& _
"ON tblTeamLeaders.TeamID = tblEmployees.TeamID) " & _
"ON ALLPolicies.Agent = tblEmployees.UserId " & _
"WHERE (ALLPolicies.BoundDate) = Date() " & _
"GROUP BY tblTeamLeaders.TeamID " & _
"HAVING
(((tblTeamLeaders.TeamID)=[Forms]![frmTeamProductionStats]![TeamID]));")
Click to expand...
Click to expand...
Click to expand...
 
Thank you, it worked.
--
Leslie


John Spencer said:
I left off a line continuation

Set rst = db.OpenRecordset("SELECT Count(ALLPolicies.PolicyNo) " & _
"AS CountOfPolicyNo, Sum(ALLPolicies.AnnualPremium) " & _
"AS SumOfAnnualPremium, tblTeamLeaders.TeamID " & _
"FROM ALLPolicies INNER JOIN (tblTeamLeaders INNER JOIN tblEmployees " &
_
"ON tblTeamLeaders.TeamID = tblEmployees.TeamID) " & _
"ON ALLPolicies.Agent = tblEmployees.UserId " & _
"WHERE (ALLPolicies.BoundDate) = Date() " & _
"AND tblTeamLeaders.TeamID= " & _
[Forms]![frmTeamProductionStats]![TeamID] & " " & _
"GROUP BY tblTeamLeaders.TeamID "

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Leslie said:
Thanks for answering John, I switched to using the GroupBy like you
suggested
and I am still getting a "too few parameters" message and in debug, it is
pointing to the line the following line

"GROUP BY tblTeamLeaders.TeamID;")
--
Leslie


John Spencer said:
Since you are not using a saved query, Access never has a chance to
convert
the control reference to a value.

Assumption:
TeamID is a number field. If TeamID is a text field then you will need
to
add string delimiters to the query.

Set rst = db.OpenRecordset("SELECT Count(ALLPolicies.PolicyNo) " & _
"AS CountOfPolicyNo, Sum(ALLPolicies.AnnualPremium) " & _
"AS SumOfAnnualPremium, tblTeamLeaders.TeamID " & _
"FROM ALLPolicies INNER JOIN (tblTeamLeaders INNER JOIN tblEmployees
" &
_
"ON tblTeamLeaders.TeamID = tblEmployees.TeamID) " & _
"ON ALLPolicies.Agent = tblEmployees.UserId " & _
"WHERE (ALLPolicies.BoundDate) = Date() " & _
"GROUP BY tblTeamLeaders.TeamID " & _
"HAVING tblTeamLeaders.TeamID= " &
[Forms]![frmTeamProductionStats]![TeamID]

Change the last line to the following if TeamID is a string
"HAVING tblTeamLeaders.TeamID= """ &
[Forms]![frmTeamProductionStats]![TeamID] & """"

For better performance I would include the criteria in the HAVING Clause
in
the where clause.

Set rst = db.OpenRecordset("SELECT Count(ALLPolicies.PolicyNo) " & _
"AS CountOfPolicyNo, Sum(ALLPolicies.AnnualPremium) " & _
"AS SumOfAnnualPremium, tblTeamLeaders.TeamID " & _
"FROM ALLPolicies INNER JOIN (tblTeamLeaders INNER JOIN tblEmployees
" &
_
"ON tblTeamLeaders.TeamID = tblEmployees.TeamID) " & _
"ON ALLPolicies.Agent = tblEmployees.UserId " & _
"WHERE (ALLPolicies.BoundDate) = Date() " & _
"AND tblTeamLeaders.TeamID= " &
[Forms]![frmTeamProductionStats]![TeamID] & " " _
"GROUP BY tblTeamLeaders.TeamID "
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I keep getting "too few parameters" error, does anyon know why.

Set rst = db.OpenRecordset("SELECT Count(ALLPolicies.PolicyNo) " & _
"AS CountOfPolicyNo, Sum(ALLPolicies.AnnualPremium) " & _
"AS SumOfAnnualPremium, tblTeamLeaders.TeamID " & _
"FROM ALLPolicies INNER JOIN (tblTeamLeaders INNER JOIN tblEmployees
"
& _
"ON tblTeamLeaders.TeamID = tblEmployees.TeamID) " & _
"ON ALLPolicies.Agent = tblEmployees.UserId " & _
"WHERE (ALLPolicies.BoundDate) = Date() " & _
"GROUP BY tblTeamLeaders.TeamID " & _
"HAVING
(((tblTeamLeaders.TeamID)=[Forms]![frmTeamProductionStats]![TeamID]));")
Click to expand...
Click to expand...
Click to expand...
 
Back
Top