How to Use Variables in Access Query

Open the Northwind Microsoft Office Access database. The Northwind database is included in the Microsoft Office suite.

Click "Database Tools" then select "Visual Basic" to open the Microsoft Visual Basic Window. Click the "Insert" field and select "Module."

Copy and paste the following code into your new module to use "Supplier A" as a variable in a select query and view the first name and last name of "Supplier A":

Private Sub userQueryVariables()

Dim strSQL As String

Dim rst As Recordset

Dim dbs As Database

Dim supplier As String

supplier = "Supplier A"

Set dbs = CurrentDb

strSQL = "SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name] "

strSQL = strSQL & "FROM Suppliers "

strSQL = strSQL & "WHERE (((Suppliers.Company)='" & (supplier) & "'));"

Set rst = dbs.OpenRecordset(strSQL)

rst.MoveLast

rst.MoveFirst

Debug.Print (supplier & " Information:")

Do While Not rst.EOF

Debug.Print ("First Name: " & rst.Fields(2).Value)

Debug.Print ("Last Name: " & rst.Fields(1).Value)

rst.MoveNext

Loop

End Sub

Press "F5" to run your subroutine.

View the original article here