Is it possible to use two or more variables in an SQL select query?

[lwilly]

I have a form where users select a year and month. Using these selections I need to get to a date stored in a seperate field in my table. I think this is possible, but nothing I try is working. I have created a query and copied the SQL statement from there to paste into my code, but when I run the code it gives me &quot;Error 3061, too few parameters, expected 2&quot;. Can someone help?<br>Thanks

 

[famousb]

well firstly, when you copied the SQL into your code, did you use <br><b><br>DoCmd.RunSQL &quot;Insert Into...&quot;<br></b><br><br>the quotes are important here.<br>and what was the actual code? <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[lwilly]

I am using a select statement like this, strSQL = &quot;SELECT tblGL_SalesHistDates.Description, tblGL_SalesHistDates.FiscalYear, tblGL_SalesHistDates.SalesHistStart FROM tblGL_SalesHistDates WHERE (((tblGL_SalesHistDates.Description)=[forms]![frmCustomerSalesDialogBox]![cmbstartperiod]) AND ((tblGL_SalesHistDates.FiscalYear)=[forms]![frmCustomerSalesDialogBox]![cmbstartyear]));&quot;<br>

 

[Sainath]

I think the way you declared your SQL statements are wrong. I am assuming you are putting the statements in VBA code.<br>This is how I would do it:<br><br><br>Dim dbs As Database<br>Dim rst As recordset<br>strSQL As String<br>Month As Integer<br>Year As Integer<br><br>Month=[forms]![frmCustomerSalesDialogBox]![cmbstartperiod]<br>Year= [forms]![frmCustomerSalesDialogBox]![cmbstartyear]<br><br>strSQL = &quot;SELECT * FROM [tblGL_SalesHistDates]&quot;<br>strSQL = strSQL & &quot;WHERE [Description] = &quot; & Month<br>strSQL = strSQL & &quot;AND [FiscalYear] = &quot; & Year<br>&nbsp;<br>'to open up the record set<br>dbs=CurrentDb()<br>rst=dbs.openrecordset(strSQL)<br><br><br>I believe this should work for you

 

[lwilly]

Sainath, <br>I am getting the same error with your code as I do with mine, except the message is &quot;Too few parameters expecting 1&quot;. The error number is the same.

 

[rbowes]

Will, Put into your code BEFORE rst=dbs.openrecordset(strSQL)Debug.Print strSQL.&nbsp;&nbsp;Open the immediate window and run the code.&nbsp;&nbsp;It will break but you will have the complete SQL statement.&nbsp;&nbsp;Copy the statement in the immediate window and open a new query, do not add any tables or queries just click &quot;Close&quot; then open the SQL View.&nbsp;&nbsp;Paste your statement and make the necessary changes the make the statement run(the paste may split statements like tblMyTable.[Authorization<br>No]) I have seen this happen.<br><br>Now run the query.&nbsp;&nbsp;If it doesn't work go into Design view and see what's different from what you expect.&nbsp;&nbsp;This method always helps me figure out what's going on with crashing SQL statements in VBA.<br><br><br>Good luck!<br><br>Bob B.<br><br>PS. I don't see any problem with Sainath 's code<br><br>

 

[lwilly]

Thank you,<br>I did what you suggested Bob, and it showed me the problem. When I created the new query and paste the SQL statement that was created in code into it, it was including [] around the month field. I don't know why, but converting month to an integer and selecting that in my statement, it took care of the problem<br><br>Thanks again everyone, I'm not sure your getting paid enough for what you do.