OpenRecordset Too Few Parameters

G

Guest

Hello all. I've been trying to execute a query from code and cannot get it
to work. I have read through the various posts on this subject and have
tried everything I could find (use ' instead of ", generate a text string of
the query that evaluates the parameters and pass that to DAO, use chr() to
make ' and ", etc.) but nothing seems to work. I have commented out the
OpenRecordset command to debug the actual query string, and it appears to be
okay - both parameters are being populated. But it still keeps coming back
with too few parameters. Below is a copy of the code:

Dim rst As DAO.Recordset
Dim db As DAO.database
Dim strQuery As String

strQuery = "SELECT tblEmployees.EmailAddress " & _
"FROM (tblEmployees INNER JOIN tblProjectSupport ON " & _
"tblEmployees.EmployeeID = tblProjectSupport.EmployeeID) " & _
"INNER JOIN tblDefFunction ON tblProjectSupport.FunctionID =
" & _
"tblDefFunction.FunctionID " & _
"WHERE tblProjectSupport.ProjectID = " & _
[Forms]![frmProjectSupport].[FilterProjectID] & _
" And tblDefFunction.FunctionDesc = " & _
"'" & [Forms]![frmProjectSupport].[EmailFunctionDesc] & "*'"
& _
"GROUP BY tblEmployees.EmailAddress " & _
"HAVING tblEmployees.EMailAddress Is Not Null " & _
"ORDER BY tblEmployees.EmailAddress;"


Set db = CurrentDb()

Set rst = db.OpenRecordset(strQuery)
..
..
..

Any thoughts?
 
G

Guest

Hi Ben,

Try inserting a line that reads:
Debug.Print strQuery

Then examine the results in the Immediate window (open with Ctrl G). You can
copy this SQL statement and paste it into the SQL view of a new query as
well. This often times will assist one with troubleshooting a statement
created using VBA code.

I created a quick form with two textboxes, named as your code indicated.
When I printed the results to the Immediate window, I got the following:

SELECT tblEmployees.EmailAddress FROM (tblEmployees INNER JOIN
tblProjectSupport ON tblEmployees.EmployeeID = tblProjectSupport.EmployeeID)
INNER JOIN tblDefFunction ON tblProjectSupport.FunctionID =
tblDefFunction.FunctionID WHERE tblProjectSupport.ProjectID = 1 And
tblDefFunction.FunctionDesc = 'test*'GROUP BY tblEmployees.EmailAddress HAVING
tblEmployees.EMailAddress Is Not Null ORDER BY tblEmployees.EmailAddress;

Notice the missing space in the 5th line, above: 'test*'GROUP
I believe this should probably be: 'test*' GROUP

so that the SQL keyword "GROUP" is not jammed up against the single quote.


Good Luck,

Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
G

Guest

PS. If you are going to use the asterisk wildcard, you need to use the LIKE
keyword, in place of the equals, as follows:

tblDefFunction.FunctionDesc LIKE 'test*' GROUP BY


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


Tom Wickerath said:
Hi Ben,

Try inserting a line that reads:
Debug.Print strQuery

Then examine the results in the Immediate window (open with Ctrl G). You can
copy this SQL statement and paste it into the SQL view of a new query as
well. This often times will assist one with troubleshooting a statement
created using VBA code.

I created a quick form with two textboxes, named as your code indicated.
When I printed the results to the Immediate window, I got the following:

SELECT tblEmployees.EmailAddress FROM (tblEmployees INNER JOIN
tblProjectSupport ON tblEmployees.EmployeeID = tblProjectSupport.EmployeeID)
INNER JOIN tblDefFunction ON tblProjectSupport.FunctionID =
tblDefFunction.FunctionID WHERE tblProjectSupport.ProjectID = 1 And
tblDefFunction.FunctionDesc = 'test*'GROUP BY tblEmployees.EmailAddress HAVING
tblEmployees.EMailAddress Is Not Null ORDER BY tblEmployees.EmailAddress;

Notice the missing space in the 5th line, above: 'test*'GROUP
I believe this should probably be: 'test*' GROUP

so that the SQL keyword "GROUP" is not jammed up against the single quote.


Good Luck,

Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


BenL712 said:
Hello all. I've been trying to execute a query from code and cannot get it
to work. I have read through the various posts on this subject and have
tried everything I could find (use ' instead of ", generate a text string of
the query that evaluates the parameters and pass that to DAO, use chr() to
make ' and ", etc.) but nothing seems to work. I have commented out the
OpenRecordset command to debug the actual query string, and it appears to be
okay - both parameters are being populated. But it still keeps coming back
with too few parameters. Below is a copy of the code:

Dim rst As DAO.Recordset
Dim db As DAO.database
Dim strQuery As String

strQuery = "SELECT tblEmployees.EmailAddress " & _
"FROM (tblEmployees INNER JOIN tblProjectSupport ON " & _
"tblEmployees.EmployeeID = tblProjectSupport.EmployeeID) " & _
"INNER JOIN tblDefFunction ON tblProjectSupport.FunctionID =
" & _
"tblDefFunction.FunctionID " & _
"WHERE tblProjectSupport.ProjectID = " & _
[Forms]![frmProjectSupport].[FilterProjectID] & _
" And tblDefFunction.FunctionDesc = " & _
"'" & [Forms]![frmProjectSupport].[EmailFunctionDesc] & "*'"
& _
"GROUP BY tblEmployees.EmailAddress " & _
"HAVING tblEmployees.EMailAddress Is Not Null " & _
"ORDER BY tblEmployees.EmailAddress;"


Set db = CurrentDb()

Set rst = db.OpenRecordset(strQuery)
.
.
.

Any thoughts?
Click to expand...
Click to expand...
 
G

Guest

Thanks for your help. I tried your suggestions several times and with a few
more tweaks it finally worked.


Tom Wickerath said:
PS. If you are going to use the asterisk wildcard, you need to use the LIKE
keyword, in place of the equals, as follows:

tblDefFunction.FunctionDesc LIKE 'test*' GROUP BY


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


Tom Wickerath said:
Hi Ben,

Try inserting a line that reads:
Debug.Print strQuery

Then examine the results in the Immediate window (open with Ctrl G). You can
copy this SQL statement and paste it into the SQL view of a new query as
well. This often times will assist one with troubleshooting a statement
created using VBA code.

I created a quick form with two textboxes, named as your code indicated.
When I printed the results to the Immediate window, I got the following:

SELECT tblEmployees.EmailAddress FROM (tblEmployees INNER JOIN
tblProjectSupport ON tblEmployees.EmployeeID = tblProjectSupport.EmployeeID)
INNER JOIN tblDefFunction ON tblProjectSupport.FunctionID =
tblDefFunction.FunctionID WHERE tblProjectSupport.ProjectID = 1 And
tblDefFunction.FunctionDesc = 'test*'GROUP BY tblEmployees.EmailAddress HAVING
tblEmployees.EMailAddress Is Not Null ORDER BY tblEmployees.EmailAddress;

Notice the missing space in the 5th line, above: 'test*'GROUP
I believe this should probably be: 'test*' GROUP

so that the SQL keyword "GROUP" is not jammed up against the single quote.


Good Luck,

Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


BenL712 said:
Hello all. I've been trying to execute a query from code and cannot get it
to work. I have read through the various posts on this subject and have
tried everything I could find (use ' instead of ", generate a text string of
the query that evaluates the parameters and pass that to DAO, use chr() to
make ' and ", etc.) but nothing seems to work. I have commented out the
OpenRecordset command to debug the actual query string, and it appears to be
okay - both parameters are being populated. But it still keeps coming back
with too few parameters. Below is a copy of the code:

Dim rst As DAO.Recordset
Dim db As DAO.database
Dim strQuery As String

strQuery = "SELECT tblEmployees.EmailAddress " & _
"FROM (tblEmployees INNER JOIN tblProjectSupport ON " & _
"tblEmployees.EmployeeID = tblProjectSupport.EmployeeID) " & _
"INNER JOIN tblDefFunction ON tblProjectSupport.FunctionID =
" & _
"tblDefFunction.FunctionID " & _
"WHERE tblProjectSupport.ProjectID = " & _
[Forms]![frmProjectSupport].[FilterProjectID] & _
" And tblDefFunction.FunctionDesc = " & _
"'" & [Forms]![frmProjectSupport].[EmailFunctionDesc] & "*'"
& _
"GROUP BY tblEmployees.EmailAddress " & _
"HAVING tblEmployees.EMailAddress Is Not Null " & _
"ORDER BY tblEmployees.EmailAddress;"


Set db = CurrentDb()

Set rst = db.OpenRecordset(strQuery)
.
.
.

Any thoughts?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Ben,

I'm glad to read that you had success. Using the Debug.Print method to print
a SQL statement to the Immediate window is a good method of debugging,
because it allows one to copy and paste the SQL statement into a new query.
As I mentioned previously, one will often times get a more meaningful error
message when running the same SQL statement in a query versus attempting to
run it in code.

Good Luck on your project.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error '3061': Too few parameters. Expected 1. 6
OpenRecordset ... Too few parameters 1
Too Few Parameters in OpenRecordset 3
Too few parameters? 2
Run-time error 3061. Too Few Parameters. Expected 2. 2
Run-time error "3061" Too few parameters. Expected 1. 4
Too few parameters 2
Help with Syntax Too Few Parameters 9

Top