---

MS Access 2007: Use a control in a SQL statement

This MSAccess tutorial explains how to using VBA code to include the value of a text box within a SQL statetement in Access 2007 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2007, I've created a form where the user enters a value in a textbox. I want to use this value to create a SQL statement. How do I use a control's value in a SQL statement in VBA code?

Answer: The first example that we'll take a look at involves using a control whose value contains a number. The following code uses a textbox control called txtSupplier_ID in a SQL statement in VBA code.

Dim LSQL as string

LSQL = "select * from Suppliers where Supplier_ID = " & txtSupplier_ID

The result set will return all records from the Suppliers table where the Supplier_ID equals the value that is found in the textbox control called txtSupplier_ID.

Our next example will look at a control whose value contains a string. The following code uses a control called txtSupplier_City in a SQL statement in VBA code.

Dim LSQL as string

LSQL = "select * from Suppliers where Supplier_City =' " & txtSupplier_City & "'"

You will notice in this example that you need to place a single quote in front of the control (txtSupplier_City) as well as one after. This is because string values must always be surrounded by single quotes in a SQL statement

This result set will return all records from the Suppliers table where the Supplier_City equals the value that is found in the textbox called txtSupplier_City.