Add / Edit Criteria buttonClick on Add or Edit  on the Insert DB Loop window to display the Add / Update Criteria window.

Add / Update Criteria WindowSection 1 - FieldSection 1 - VariableSection 2 - ComparisonSection 3 - FieldSection 3 - Current RowSection 3 - VariableSection 3 - Expression

Use this window to add or update a criteria clause in your database LOOP command. Make the required changes and when you press Update the criteria list on the LOOP window. The window can effectively be split into three sections (from left to right).

You must press Add New (or Update if editing an existing criteria) for your changes to be applied to the current LOOP you are editing.

To learn more about the features of this window, glance down the table below or highlight the control you are interested in by clicking on the screenshot above.

Section 1

Add Criteria - Section 1This drop-down list contains all the fields that are in the currently selected table.

This list will include any custom fields that you might have added to a table using Microsoft Access.

Select the field that is to be used for narrowing down the records retrieved.

Or, if you want to use the value of a variable, you can press the Variable check box.

Variable

Check the Variable option if you want to use a variable instead of a field. The window will change to look like this:

Add Criteria - Section 1 - Variable ListSelect the variable you want to use from the drop-down list or press Expression to display the Expression Editor.

Section 2

Add Criteria - Section 2Use this drop-down list to select how you want to find the relevant records.

The comparison is case insensitive, so "A Name" and "a name" would be classed as the same.

There are ten choices:

  • MUST BE
    Use this if you want all records that match a value.
  • MUST NOT BE
    Use this if you want all records that don't match this value.

The following 3 choices only work with text and numeric fields. The Add process will fail if you try to use these choices with any other type of field.

  • MUST CONTAIN
    Use this if you want all records where the contents partially match a value.
  • MUST START WITH
    MUST END WITH
    Use either of these options if you want all records that start or end with a particular value.
  • MUST BE LESS THAN
    MUST BE, OR BE LESS THAN

These criteria should be used with numeric or date fields.

  • MUST BE MORE THAN
    MUST BE, OR BE MORE THAN

These criteria should be used with numeric or date fields.

  • MUST BE MULTIPLE OF
    This criteria should be used with numerics.

Section 3

Add Criteria - Section 3This edit box will change depending upon what type of field is currently displayed. Here you specify the value to be used in the comparison.

Keep in mind that the settings Current row and Variable will replace this edit box with another drop-down list.

The four types of field that are supported are:

Text / Number / Date / Yes - No

Current Row

The Current row option is only enabled if you are inside an embedded LOOP. In other words, this loop is inside an outer LOOP. If this is the case, the row that is currently being accessed in the outer LOOP is termed the Current Row, so you can access this information when narrowing down your records.

When this option is on you will see a list of available fields from the outer LOOP.

Variable

Set the Variable option if you want to use a variable in the where clause. When this option is on, you can choose a variable from those available in the drop-down list. This makes the LOOP command very powerful.

Note IconRead up on the REQUIRES command if you are not sure about variables.

Expression

If you want to use an expression to represent the value to compare against then press Expression to display the Expression Editor.

Working with Date Fields

Please be aware about how it works for date fields. When you first select a date field from the drop-down list on the left, the control on the right is a standard date picker.

If you want to compare with the result of an expression, click the Expression button on the right. When you close the Expression Editor by pressing OK then date picker will be replaced by a normal text box, as if we were comparing against a string - the expression will be entered into this box.

If you accept this criteria and then edit it later, it will note that it is an expression and automatically bring back the expression and text box instead of the date picker.

If you subsequently select any date field from the drop-down list on the left - including re-selecting the same date field - the text box will revert to the date picker. This is how you can revert to a simple date.

Note IconTo avoid losing your expression, don't change the field drop list on the left while an expression is showing on the right.