How To Use VB Control Property or Variable in SQL Statement

Article translations Article translations
Article ID: 146909 - View products that this article applies to.
This article was previously published under Q146909
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

SUMMARY

You can have a Visual Basic application build a SQL query based on choices made by a person using the application. The application can then use the SQL query when creating a view into a database.

This article describes methods developers can use to create SQL queries that are based on control properties or names of variables. The information in this article applies to the following methods: FindFirst, FindLast, FindNext, FindPrevious, CreateDynaset, CreateSnapshot, Execute, and ExecuteSQL.

MORE INFORMATION

When building a SQL query, do not include the variable or control name inside the SQL string; instead, you should reference its value. Using the variable or control name inside the SQL string is a mistake. For example, the following code is incorrect:
   Dim ds As Recordset
   Set ds = MyDB.OpenRecordset("Employees", dbOpenDynaset)
      ds.FindFirst "NameField = Text1.Text"   ' This code is incorrect.
				
This code is trying to create a dynaset that finds the first occurrence of the contents of Text1 in a field called NameField. Although the code does not produce an error, it does not find the desired value. It searches for the first occurrence of the string "Text1.Text" not the value of the Text1.Text control property.

The criteria being sought is a string, so the programmer must use string concatenation rules to build the criteria string. The following gives the correct version of the code example:
   Dim ds As Recordset
   Set ds = MyDB.OpenRecordset("Employees", dbOpenDynaset)
      ds.FindFirst "NameField = '" & Text1.Text & "'"
				
The ampersand (&) operator concatenates the strings together correctly. Also, in SQL syntax, you need to enclose string data in single quotation marks to differentiate strings from variables.

If you think the corrected version looks confusing with all the single and double quotation marks, you can assign the criteria to a string. Then use Debug.Print to view the contents of the string. The following is the same example enhanced to take advantage of the debug window:
   Dim ds As Recordset
   Dim SQL$ as String
   Set ds = MyDB.OpenRecordset("Employees", dbOpenDynaset)
      SQL$ = "NameField = '" & Text1.Text & "'"
      Debug.Print SQL$
      ds.FindFirst SQL$
				
If Text1 contains the string "Wilson," the Debug windows displays:
   NameField = 'Wilson'
				
If the data type of a field is a number instead of a string, do not enclose the value being sought in single quotation marks. For example, use the following code to create a dynaset that finds the first occurrence of a zip code in a field called ZipCodeField where the ZipCodeField data type is not a string:
   Dim ds As Recordset
   Dim SQL$ as String
   Dim ZipCodeVar as Double
   Set ds = MyDB.OpenRecordset("Employees", dbOpenDynaset)
      ZipCodeVar = 98052
      SQL$ = "ZipCodeField = " & ZipCodeVar  ' This line builds the string.
      Debug.Print SQL$
      ds.FindFirst SQL$
				

Properties

Article ID: 146909 - Last Review: February 28, 2014 - Revision: 3.1
APPLIES TO
  • Microsoft Visual Basic 4.0 Standard Edition
  • Microsoft Visual Basic 4.0 Professional Edition
  • Microsoft Visual Basic 4.0 Enterprise Edition
Keywords: 
kbnosurvey kbarchive kbhowto KB146909

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com