Query by Form

With Query by Form you order records based on the contents of one to five different fields. For example, using Query by Form you can sort customer records by State, then City, then Lastname, and finally Firstname.

You specify record-ordering criteria in Order Criteria view, where number boxes appear next to the form fields.

To order records in your form:

Select Query > Query by Form... or click .
Click on the toolbar.
Click the box at the upper left corner of each field to assign its sorting sequence. For example, click on the State field box to assign the number 1 and designate the State field as the first (most important) criteria for ordering records. Clicking on subsequent order number boxes for other fields assigns the numbers 2, 3, 4, and 5, indicating the hierarchy for ordering records.
Optionally, click the arrows next to the numbers to specify ascending (up arrow) or descending (down arrow) order. Click Run Query any time to perform the query.

Order Number Boxes

Click the Clear Criteria button at any time to reset ordering.
Quick Run Query to display the first sorted record.
Click , , or to view the other sorted records.

Selecting Records

Query by Form makes record selection easy. To find records that match specific criteria, you type the criteria directly in the fields of the form. For example, if you are viewing a table of customers and you want to view only customers from Massachusetts, you would type MA (the value for which you are looking) in the State field and run the query.

To select records using your form:

Select Query > Query by Form... or click .
Click on the toolbar.
Optionally, if you understand filter syntax, enter filter criteria directly into the form fields.

You can specify multiple conditions; for example, you can type "MA" in the State field and "Springfield" in the City field. This query selects only customers who live in Springfield, Massachusetts.
You can also use query operators, such as a comma, when specifying conditions. The comma is used as a logical OR. For example, by entering "NY,MA" in the State field, Alpha Five selects customers who live in New York or Massachusetts.

Optionally, for assistance is entering filters, click to display the QBF Helper. Query by Form has a special syntax and using the QBF Helper helps you learn it. You can use the QBF Helper on any of the form fields.

Click in a form field and then click one of the expression buttons in the QBF Helper.
The QBF Helper prompts you for a value to finish the expression.
Alpha Five fills the resulting expression in the form field.

Optionally, use any Alpha Five function as Part of your search criteria. For example, the SOUNDEX() function allows for spelling variations. In a form with a LASTNAME field, you could enter SOUNDEX(LASTNAME)=SOUNDEX("Johnson") to search for last names of "Johnson" and spelling variations of "Johnson".

Querying with Multiple Forms

When creating elaborate OR queries, you can use multiple forms for the query. To move to additional query condition pages, click , , or to view the other forms to use in the query. Alternatively, press the Page Up or Page Down keys on your keyboard. When the query runs, Alpha Five will return records satisfying conditions on all the forms.
Optionally, click the Include Records button on the toolbar. The query will return only records that match the search criteria.
Optionally, click the Exclude Records button on the toolbar. The query will return only records that do not match the search criteria.
Optionally, click the Show Expressions button on the toolbar to see the Xbasic expressions that you are using in your query.
Click Run to apply the filter and to display the first record that matches the query criteria.
Click , , or to view the other records that satisfy the query.
Click Clear to clear all filter criteria.
Click on the toolbar to show all records.

Query by Form Syntax

Operator	Description	Examples	Explanation
<	less than	<7 <Fred <4/16/2002	less than seven occurs before "Fred" (alphabetically) occurs before the date 4/16/2002
>	greater than	>7 >Fred >4/16/2002	greater than seven occurs after "Fred" occurs after 4/16/2002
<>	not equal to	<>7 <>Fred <>4/16/2002	not equal to seven not "Fred" does not occur on 4/16/2002
>=	greater than or equal to	>=7 >=Fred >=4/16/2002	is greater than or equal to seven is "Fred" or comes after "Fred" occurs on or after 4/16/2002
<=	less than or equal to	<=7 <=Fred <=4/16/2002	is less than or equal to seven is "Fred" or comes after "Fred" occurs on or before 4/16/2002
*	wildcard character	fr* *ed	value starts with "fr" value ends with "ed"
, (comma)	logical OR	car, truck	value equals "car" or "truck"
..	between	1..7 A..M 4/16/2001..4/16/2002	is between one and seven is between A and M occurs between 4/16/2001 and 4/16/2002
is blank	is blank	is blank	the field is empty
is not blank	is not blank	is not blank	the field is not empty
$	contains	$ software	the field contains the word "software" anywhere within the field

Saving and Opening Queries

Alpha Five lets you save queries you create in Query-by-Form for future use. To save a query:

Define your search criteria and click the Save button on the toolbar.
Alpha Five prompts you to name the query and saves it.
Saved queries appear on the Operations tab in the Control Panel.

To open a saved query:

Click the Open button on the toolbar.
Alpha Five prompts you for the location of the query and opens it.

To create a new query:

Click the New button on the toolbar.
Alpha Five opens a new, blank query form.

To remove saved queries place the following Xbasic statement under the OnPush event of a button:

dim tbl as P

tbl = table.current()

tbl.query_detach_all()