Filtering Records With Boolean Operators

Equality = The equality operator is used to compare two values for similarity. The syntax of this operation is: Value1 = Value2   If Value1 and Value2 hold the same value, the comparison produces a TRUE result. If they hold different values, the comparison renders a FALSE value. Here is an example: Private Sub btnRecordset_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnRecordset.Click Dim rstPeople As ADODB.Recordset = New ADODB.Recordset Dim conADO As ADODB.Connection = New ADODB.Connection conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0) rstPeople.Open("SELECT * FROM Persons " & _ "WHERE Gender='Male';", _ conADO, _ ADODB.CursorTypeEnum.adOpenDynamic, _ ADODB.LockTypeEnum.adLockOptimistic, 0) rstPeople.Close() End Sub   Inequality <> To find out if two fields hold different values, you can use the inequality operator which is represented by <>. Its syntax is: Value1 <> Value2   This comparison is performed between Value1 and Value2. If they hold different values, then the comparison produces a TRUE value. If they hold the same value, the comparison produces FALSE. This shows that the equality (=) and the inequality (<>) operators are opposite each other. Here is an example: Private Sub btnRecordset_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnRecordset.Click Dim rstPeople As ADODB.Recordset = New ADODB.Recordset Dim conADO As ADODB.Connection = New ADODB.Connection conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0) rstPeople.Open("SELECT * FROM Persons " & _ "WHERE Gender<>'Female';", _ conADO, _ ADODB.CursorTypeEnum.adOpenDynamic, _ ADODB.LockTypeEnum.adLockOptimistic, 0) rstPeople.Close() End Sub   Less Than < The "Less Than" operator uses the following syntax: Value1 < Value2   If Value1 holds a value that is lower than that of Value2, the comparison produces TRUE. If Value1 holds a value that is greater than or similar to that of Value2, the comparison renders FALSE. Here is an example: Private Sub btnRecordset_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnRecordset.Click Dim rstPeople As ADODB.Recordset = New ADODB.Recordset Dim conADO As ADODB.Connection = New ADODB.Connection conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source='C:\Programs\Students.mdb'", Nothing, Nothing, 0) rstPeople.Open("SELECT * FROM Students " & _ "WHERE DOB < '1988-06-01';", _ conADO, _ ADODB.CursorTypeEnum.adOpenDynamic, _ ADODB.LockTypeEnum.adLockOptimistic, 0) rstPeople.Close() End Sub   Less Than or Equal <= When comparing two values, you may want to know whether two fields hold the same value or if one is lower than the other. This comparison can be performed with the "Less Than Or Equal To" operator. It is represented by <= and its formula is: Value1 <= Value2   If both operands (Value1 and Value2) hold the same value, then the comparison produces a TRUE result. If Value1 holds a value that is lower than that of Value2, the comparison still produces a TRUE result. By contrast, if the value of Value1 is higher than that of Value2, the comparison renders a FALSE result. Notice that the > and the <= operators are opposite each other. Here is an example: Private Sub btnRecordset_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnRecordset.Click Dim rstPeople As ADODB.Recordset = New ADODB.Recordset Dim conADO As ADODB.Connection = New ADODB.Connection conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source='C:\Programs\Students.mdb'", Nothing, Nothing, 0) rstPeople.Open("SELECT * FROM Students " & _ "WHERE DOB =< '1988-06-01';", _ conADO, _ ADODB.CursorTypeEnum.adOpenDynamic, _ ADODB.LockTypeEnum.adLockOptimistic, 0) rstPeople.Close() End Sub   Greater Than > The > operator is used to find out whether one value is "Greater Than" another. Its syntax is: Value1 > Value2   The operation is performed on the values of Value1 and Value2. If Value1 holds a value greater than that of Value2, then the comparison produces TRUE. Otherwise, the comparison produces FALSE. That is, if the value of Value2 is greater than or equal to that of Value1, then the comparison produces FALSE. Greater Than or Equal >= If you have two values and want to find out whether they hold similar values or the first is greater than the second, you can use the >= operator whose syntax is: Value1 >= Value2   If both Value1 and Value2 hold the same value, then the comparison renders a TRUE result. Similarly, if the left operand, Value1, holds a value greater than that of the right operand, Value2, the comparison still produces TRUE. If the value of Value1 is less than the value of Value2, the comparison produces a FALSE result. Therefore, < and >= are opposite. The NOT Operator To deny the presence, the availability, or the existence of a value, you can use the NOT operator. This operator is primarily used to reverse a Boolean value. For example, we have learned that FALSE is the opposite of TRUE. In the same way, TRUE is the opposite of FALSE. If you want to compare a value as not being TRUE, the NOT TRUE would produce the same result as the FALSE value. For the same reason, the expression NOT FALSE is the same as TRUE.  Here is an example: Private Sub btnRecordset_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnRecordset.Click Dim rstPeople As ADODB.Recordset = New ADODB.Recordset Dim conADO As ADODB.Connection = New ADODB.Connection conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0) rstPeople.Open("SELECT * FROM Persons " & _ "WHERE NOT (Gender = 'Male');", _ conADO, _ ADODB.CursorTypeEnum.adOpenDynamic, _ ADODB.LockTypeEnum.adLockOptimistic, 0) rstPeople.Close() End Sub    The IS Operator To validate something as being possible, you can use the IS operator. For example, to acknowledge that something is NULL, you can use the IS NULL expression. Here is an example: Private Sub btnRecordset_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnRecordset.Click Dim rstPeople As ADODB.Recordset = New ADODB.Recordset Dim conADO As ADODB.Connection = New ADODB.Connection conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0) rstPeople.Open("SELECT * FROM Persons " & _ "WHERE Gender IS NULL;", _ conADO, _ ADODB.CursorTypeEnum.adOpenDynamic, _ ADODB.LockTypeEnum.adLockOptimistic, 0) rstPeople.Close() End Sub   In the same way, to validate that something is not null, you can use the expression IS NOT NULL.   Here is an example: Private Sub btnRecordset_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnRecordset.Click Dim rstPeople As ADODB.Recordset = New ADODB.Recordset Dim conADO As ADODB.Connection = New ADODB.Connection conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0) rstPeople.Open("SELECT * FROM Persons " & _ "WHERE Gender IS NOT NULL;", _ conADO, _ ADODB.CursorTypeEnum.adOpenDynamic, _ ADODB.LockTypeEnum.adLockOptimistic, 0) rstPeople.Close() End Sub   Comparison Operators: IN If you have a series of records and want to find a record or a group of records among them, you can use the IN operator.