VBA - SQL string referencing a Form Control in Where clause

Question

The following code works:

Dim rs As DAO.Recordset

Dim strSQL As String

strSQL = "Select * From [Table1] Where [Field1] = 17"

Set rs = CurrentDb.OpenRecordset (strSQL)

However, I need the numerical value in the Where clause to come from a form control and i can't seem to make this work.  I keep getting "Run-time error '3061': Too few parameters. Expected 1."

I've tried referencing it directly, through a variable, etc without luck:

strSQL = "Select * From [Table1] Where [Field1] = Forms![Form1]![Control1]"

I am sure that all of the field/control names are correctly spelled, etc.  What is the correct way to reference a form control value in such a Where clause?

Thanks!

Answer 1

Hi,

DAO can't handle the form reference inside the SQL string. Concatenate the resulting value to the string:

strSQL = "Select * From [Table1] Where [Field1] = " & Forms![Form1]![Control1]

Answer 2

Hi,

try with:

if Control1 is numeric

strSQL = "Select * From [Table1] Where [Field1] = " & Forms![Form1]![Control1]

if Control1 is a text

strSQL = "Select * From [Table1] Where [Field1] = '" & Forms![Form1]![Control1] & "'"

if Control1 is a data/time

strSQL = "Select * From [Table1] Where [Field1] = #" & Forms![Form1]![Control1] & "#"

Mimmo