VB6 beginners tutorial - Learn VB6

Advanced VB6 tutorial - Learn Advanced VB6

Systems Analysis - System analysis and Design tutorial for Software Engineering

You are here: Visual Basic > Advanced VB6 tutorial > Chapter 9

Using the Where Clause to Filter Rows

You can use a Where clause in a SQL statement to filter which records are returned in the query's result set. The syntax of a SQL statement containing a Where clause is this:

Select FieldList From TableName Where Condition

The Condition of the Where clause can be one or more comparison statements using the usual comparison operators such as =, >, <, ³, and ..., as well as other operators more specific to the SQL language such as Like (for text comparisons) and Between...And (for specifying a range of values).

A Where clause that only returns records with a field matching a particular value would use the = operator, as in the following example:

Select * From Employees Where Dependents = 0

A Where clause to return values above a certain value would use the > operator, as in the following example:

Select * From Employees Where Salary > 40,000

You would use similar rules for the <, =, ..., and ³ operators. Note that when quoting literal strings for comparison in a SQL Where clause, you use the single quotation mark character, as in the following example:

Select * From Employees Where LastName = 'Smith'

When using a literal date value in a Where clause comparison, you must use the U.S. date format (this format being mm/dd/yy)—even when you are not looking at data with a U.S. date format. You must then set the date off with the # character. The following example illustrates the use of a date value in a SQL Where clause:

Select * from Employees Where HireDate < #1/1/89#

You can use Between...And to specify a range of values to allow in the result set, as in the following example, which would allow all employee records with salaries between 40,000 and 80,000, inclusive, into the result set:

Select * From Employees Where Salary BETWEEN 40000 AND 80000

To obtain a text match with a field that contains a certain string combination (but does not exactly match the string), you can use the Like operator and specify "wildcard" characters similar to UNIX, DOS, or Windows operating system "wildcard" characters as used in file specifications.

Use the % character to specify any number of characters and the _ character to specify a single character. The following example shows a query for all records with a Last Name field beginning with the letter S:

Select * From Employees Where LastName Like 'S%'

String comparisons in Where clauses are not case sensitive.

NOTE - Other Types of Clauses: In addition to the basic SQL structure, which specifies fields and a table, you can add a number of different types of clauses to the statement. Only the Where, Order By, and Join clauses are discussed in this chapter. You should be aware that other types of clauses exist for SQL Select queries.


<< Previous | Content | Next >>

Home | About Us | Privacy Policy | Contact Us

Copyright © | All Rights Reserved