How to Use SQL- WHERE Clause Statement




SQL- WHERE Clause Statement

The SQL WHERE CLAUSE Statement is the SQL code that specifies criteria that field values must meet for the records that contain the values to be included in the query results.

#1 WHERE Clause and Is Null

Set any field to show records where the field value is null value.

Syntax:

WHERE  fieldName = null

Example:

Load form with a blank form by using a criteria Customer_ID  is null combining with a Select Statement :

Private Sub Form_Load()
Dim strTask As String
strTask = "SELECT * FROM tbl_customer WHERE customer_ID is null"
Me.RecordSource = strTask
End Sub

load with blank

#2 WHERE Clause and String

Use WHERE CLAUSE with other SQL Statements.  The result will display only the records that have a field exactly matches a criterion.

Syntax:

WHERE fieldName = criterion

Example:

Private Sub Command0_Click()
Dim delCustomer As String
         delCustomer = "delete * from tbl_Customer WHERE [State] = ‘CO’”
         DoCmd.SetWarnings False
         DoCmd.RunSQL delCustomer
         DoCmd.SetWarnings True
End Sub

where state CO

There is no record after deleting by above code.

no state CO

#3 WHERE Clause and LIKE

Use WHERE CLAUSE and LIKE together under the SELECT Statement.  The result will display only the records that have a field contains a criterion.

Syntax:

WHERE fieldName1 LIKE Some_value

Example:

The result will return records for all customers that contain the string “Mast”.

Private Sub CmdSearch_Click()
Dim Task As String
    Task = "SELECT * FROM tbl_customer WHERE ((CustomerName Like ""*Mast*""))"
    Me.List0.RowSource = Task
    Me.Requery
End Sub

list Mast



Related posts