SQL problem

[jgc31]

I have a little problem that is driving me crazy although I am sure the answers simple

I run the following code


SQLstatement = "SELECT TblIssues.Issues, TblIssues.[record number] FROM TblIssues WHERE (((TblIssues.[record number])=[Forms]![frmEnterIssues]![txtRecordNumber]));"

Set db1 = CurrentDb
Set rcd1 = db1.OpenRecordset(SQLstatement)

and get the following error message " Runtime error 3061 , too few parameters, expected 1"

I have also run it with a variable in the where statement ie

RecordNumber = Me!txtRecordNumber

WHERE (((TblIssues.[record number])=""" & RecordNumber & """" & "));"

which gives the same error

The query runs perfectly as a query. I have also tried building the statement using the query designer switching to SQL and then copying that code ( which is identical to mine) into VBA but with the same result.

Any suggestions gratefully recieved

 

[Jon K]

SQLstatement = "SELECT Issues, [record number] FROM TblIssues WHERE [record number]=" & [Forms]![frmEnterIssues]![txtRecordNumber] & ";"


If [record number] is a text field, its value needs to be surrounded by single-quotes, i.e.

SQLstatement = "SELECT Issues, [record number] FROM TblIssues WHERE [record number]='" & [Forms]![frmEnterIssues]![txtRecordNumber] & "';"

.

 

[jgc31]

With a bit of search through the forum ( perhaps I should have done it before I put the post up!) I have found a solution but really dont understand why.

If

SQLstatement = "SELECT TblIssues.Issues, TblIssues.[record number] FROM TblIssues WHERE (((TblIssues.[record number])=[Forms]![frmEnterIssues]![txtRecordNumber] ));"

is changed to

WHERE (((TblIssues.[record number])=" & [Forms]![frmEnterIssues]![txtRecordNumber] & "));"

It works ( thanks to dcx693 ) but why ??

 

[jgc31]

@ Jon K Thanks any idea why , I have not had this problem when opening recordsets based on SQL strings before

 

[boblarson]

when using a control as a variable within a SQL string, you must reference the control by ending the SQL string with "" and then inserting

& Forms!controlname &

and then starting the SQL String again by using "" making sure to leave a space in between the beginning "" and the ampersand and then a space after the ampersand that follows the control reference prior to the other "".