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.
WHERE fieldName = null
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
#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.
WHERE fieldName = criterion
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
There is no record after deleting by above code.
#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.
WHERE fieldName1 LIKE Some_value
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