How do I insert a variable into a SELECT statement?

[David Portwood]

To filter a recordsource, I want something like:

SELECT ...WHERE [Name] =' & strName & ';

I would assign the above to the recordsource property of a form. My intent
here is to save a step over opening the form on a table and then setting a
filter.

Can this be done, or must I use a filter? I read somewhere that filtering
after opening a large recordsource was inefficient.

 

[Allen Browne]

Use the Open event provedure of the form.
Create the string.
Assign to the form's RecordSource property.

This kind of thing:

Private Sub Form_Open(Cancel As Integer)
Dim strSql as String
strSql = "SELECT * FROM Table1 WHERE Field1 = """ & SomeVariable & """;"
'Debug.Print strSql
Me.RecordSource = strSql
End Sub

Notes
====
a) This assumes you already have a variable containing the name you need to
filter on. You can't use a text box on the form, because this is running
when the form is opening.

b) Use your own table name instead of Table1, and your field name instead of
Field1.

c) Hopefully you don't have a field called Name, because that will cause you
grief.

d) If Field1 is a Number field (not a Text field), omit the extra quotes.
Explanation of what those quotes are:
http://allenbrowne.com/casu-17.html