Upper and lower case
When filtering on a string variable, like the last name of the persons in the database, you often do not want to care about whether you enter upper or lowercase characters. You can accomplish that by applying at both the left and the right hand side of the condition a function that converts the characters to lowercase (LOWER for Oracle and SQL Server and LCase for Microsoft Access).
The Like operator
It is also possible that you do not want to enter the complete name or that you do not know how to spell a certain part of the name. By using the Like operator instead of the equal sign, you can realize that, since Like allows for the use of wildcards. The most important wildcards are the percent sign (%), that represents zero or more characters, and the underscore (_), that represents a single character. Furthermore, you can give alternative characters by putting them between square brackets ([]). Some examples of the use of wildcards are:
•LOWER(“LASTNAME”) LIKE ‘s%’ selects all names that start with an s
•LOWER(“LASTNAME”) LIKE ‘cart%er’ selects both Carter and Cartner
•LOWER(“FIRSTNAME”) LIKE ‘Eri_’ selects all four character names that start wit Eri
•LOWER(“FIRSTNAME”) LIKE ‘Eri[ck]’ selects all persons called Eric or Erik
Numeric values
When you want to filter on numeric values, you have to take care that you use the correct decimal separator (comma or point). In the advanced database filter, you have to follow the configuration of you operating system. However, in the SQL view, you always have to use a point.
Dates
Microsoft Access, SQL Server and Oracle store dates as date-time values. If you want to compare these values, not only the date part, but also the time part has to match. If you only want to check for equal date parts, you can achieve this by using the following conditions:
•Microsoft Access: Int([BirthDate]) = DateValue(’10-apr-1968’)
•SQL Server: [BirthDate] >= ’10-apr-1968’ AND [BirthDate] < ’11-apr-1968’
•Oracle: TO_DATE(“BIRTHDATE”) = TO_DATE(’10-apr-1968’)
In all cases, BirthDate is a date-time field and only those records will be selected for which the date part is 10 April 1968.
Microsoft Access stores the date-time value as a floating point number. The part before the decimal point represents the date part, whereas the decimals represent the time part. The function Int removes the time part, setting it to 0:00:00 AM. The function DateValue always results in a date-time value whose time part is 0:00:00 AM, so that the condition above effectively compares only the date part of BirthDate.
In Oracle, the same effect can be achieved by using the TO_DATE function on both sides. In SQL Server, a similar function does not exist, but this can be circumvented by defining two conditions. In this way, all records are selected, for which BirthDate is between 10 April 1968 0:00:00 AM and 11 April 1968 0:00:00 AM.
