How To Use VB Control Property or Variable in SQL Statement

This article was previously published under Q146909
This article has been archived. It is offered "as is" and will no longer be updated.
You can have a Visual Basic application build a SQL query based onchoices made by a person using the application. The application canthen use the SQL query when creating a view into a database.

This article describes methods developers can use to create SQL queriesthat are based on control properties or names of variables. Theinformation in this article applies to the following methods: FindFirst,FindLast, FindNext, FindPrevious, CreateDynaset, CreateSnapshot, Execute,and ExecuteSQL.
When building a SQL query, do not include the variable or control nameinside the SQL string; instead, you should reference its value. Usingthe variable or control name inside the SQL string is a mistake. Forexample, 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 ofthe contents of Text1 in a field called NameField. Although the code doesnot produce an error, it does not find the desired value. It searchesfor the first occurrence of the string "Text1.Text" not the value of theText1.Text control property.

The criteria being sought is a string, so the programmer must use stringconcatenation rules to build the criteria string. The following givesthe 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 quotationmarks to differentiate strings from variables.

If you think the corrected version looks confusing with all the singleand double quotation marks, you can assign the criteria to a string. Thenuse Debug.Print to view the contents of the string. The following is thesame 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 enclosethe value being sought in single quotation marks. For example, use thefollowing code to create a dynaset that finds the first occurrenceof a zip code in a field called ZipCodeField where the ZipCodeField datatype 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$				
pitfall RecordSource kbVBp400

Article ID: 146909 - Last Review: 02/28/2014 04:16:09 - Revision: 3.1

  • Microsoft Visual Basic 4.0 Standard Edition
  • Microsoft Visual Basic 4.0 Professional Edition
  • Microsoft Visual Basic 4.0 Enterprise Edition
  • kbnosurvey kbarchive kbhowto KB146909