|
42.3 Using Query Builder |
  
|
|
42.3 Using Query Builder |
|
42.3.1 The other way to filter is to use Query Builder. This is turned on when the "Use Query Builder as default Filter" option is On (selected).
This option will require a basic knowledge of the SQL language. If you are not familiar with SQL, it is recommended to use Filter, rather than Query Builder.
42.3.2 Open Query Builder
press the "Filter" or "Query" button
The Query Builder screen appears and it has six (6) columns
42.3.3 In the "Set" column, enter a query name. If you do not enter any value, it will use 'Default' as its query name. If you place a name, you can re-use the query next time by selecting the name from the list box at the top of the screen. If the query is composed of multiple fields then make sure each field has the same query name.
42.3.4 The second "Set" column is used to set the precedence in the Where clause of the query. The same number will have a bracket around it so that they will be resolved before the next level of precedence.
For example, in the case of (((State = "NSW") OR (State = "QLD")) AND (Company Name = "ABC*")), the clause ((State = "NSW") OR (State = "QLD")) will be resolved first giving a list of all entries that are in NSW or QLD and (Company Name = "ABC*") will be resolved second, leaving only the results from NSW and QLD that have a company name with ABC.
42.3.5 In the "Comp" column this is a dropdown list and you may select "And" or "Or" depending on your query. Selecting 'Show' is good for displaying the results in a datasheet.
42.3.6 The Field Name column corresponds to a field in the screen. Identify the correct field you want to define a criteria. If you cannot find the field under the "Field name", add it. For information on how to add a field, refer to "Add a Field in the Filter" section in this Latitude How To Guide.
42.3.7 Select "Operator" from the drop down list. If you are using wildcard, use "Like" or "Not Like" for text fields. Depending on the data, select mathematical, comparison or conditional operators whenever applicable.
42.3.8 Enter Criteria and press the "Apply" button.
42.3.9 For example, to build a query for 'NSW' phone numbers beginning with '94' or '98':
"SELECT * FROM tblClients WHERE ( ( ([Phone] Like "94*") Or ([Phone] Like "98*")) And (([Postal State] = "NSW") ) );"
you need to enter three rows, one for each criteria.
The first row will have Set = Test1, Set = 1, Comp = And, Field in Criteria = Phone, Operator = Like, Criteria = 94*
The second row will have Set = Test1, Set = 1, Comp = Or, Field in Criteria = Phone, Operator = Like, Criteria = 98*
The third row will have Set = Test1, Set = 2, Comp = And, Field in Criteria = Postal State, Operator = Equal, Criteria = NSW
){kind=link}