Works 3.0 FAQ: Database Querying Questions

MORE INFORMATION

1. Q. How do I perform text queries in Works?
   
   A. In a very large database, it may be useful to perform a query that has Works display only records that match the criteria you specify.
   
   For example, you can have Works show only records that have a value in a State field of WA, or only records for customers whose first name is Betty. The example below lists the steps to find all of the records containing "Betty" in the field "First Name,"
   
   
   1. On the Tools menu, click Create New Query.
   2. In the Choose A Field To Compare box, select the First Name field.
   3. In the How To Compare The Field Box, select the appropriate operator, for example, "Is Equal To" or "Contains".
   4. In the Value To Compare The Field To box, type the search target information. For example, Betty.
   5. Click Apply Now.
2. Q. How do I query on a blank field?
   
   A. To find records which contain a blank in a specific field, follow these steps:
   
   
   1. Follow steps 1-2 in Question 1 of this article.
   2. In the How To Compare Field box, select Is Equal To, but leave the Compare The Field To box blank.
   3. Click Apply Now.
3. Q. How do I query on a range of dates?
   
   A. You can query on a range of dates by using the AND operator in conjunction with two comparison statements. For example, if you want to find all records whose date falls from 7/1/93 to 9/30/93, follow the steps below:
   
   
   1. On the Tools menu, click Create New Query.
   2. In the Choose A Field To Compare box, select the Date field.
   3. In the How To Compare The Field box, select Is Greater Than Or Equal To.
   4. In the Value To Compare The Field To box, type the starting range of the date:

      7/1/93

   5. Click the AND operator between the first and second rows.
   6. Repeat step 2 on the second row of query criteria.
   7. On the second row of query criteria, repeat step 3, but select Is Less Than Or Equal To.
   8. On the second row of query criteria, repeat step 4 but type the ending range of the date:

      9/30/93

   9. Click Apply Now.
4. Q. How do I query on a specific month?
   
   A. In the Works database module, you can query for dates that fall in a particular month with the MONTH() function. The MONTH() function returns an integer from 1 to 12 specifying the month of the given date. For example, if you need to find records that have May in a field named "Birthday," use the steps below:
   
   
   1. On the Tools menu, click Create New Query.
   2. Click Query View.
   3. Press DELETE to remove any formula that appears.
   4. In the Birthday field, type the following formula:

      =month()=5

   5. On the View menu, click List to see the results of your query.
      
      NOTE: The examples in this question match all records where a date entry contains a month of May, regardless of the year. To find records in a certain year, use the YEAR function. For example, to find records only for May of 1995, use the following formula:

      =month(birthday)=5#and#year(birthday)=95