SQL= Reference

To add Jet interpreted filters to the WHERE or HAVING clauses, add %Filter9% to your existing WHERE term. These parameters will be replaced by appropriate WHERE terms enclosed in ().

Please note that every %FilterX% must have at least one corresponding Jet filter (see the Filterfield information below for more details).

If your filter is *, the corresponding %FilterX% will be converted to field LIKE '%' If the preceding word in the SQL query is WHERE, and there are no following words like AND or OR, then WHERE will be removed.  If the preceding word is AND, OR, etc., then that word will be removed.  If the preceding word is WHERE and the following word is AND or OR, then the following word will get stripped.

• If WHERE was blank in the following: "SQL=Where() AND %Filters1%", the "()" and the "AND" would be removed
• In the following: "SQL=Where %Filters1%", the WHERE would get removed

To add user-determined sorting to your SQL statement, add %Sort% to the Order By clause.  Since Joins use a common sort (and Sub queries do not use sorting), you do not need to put a number on %Sort%.  Despite this, your NL filters for sorting must still be prefixed by a filter number (see the section on FilterField below)

This is a field cache and returns a Record Key just as when an array of field names is passed in to this argument with a normal NL function.  ( for example:  {"Field1","Field2"} )

The field names actually returned depend on the SQL SELECT statement, itself

This parameter contains the WHERE clause replacements in the format "1S=FieldName" The first character (e.g., 1 ) corresponds to the %Filter X % that you want to replace.  The second characters determines the Field type and can be one of the following (please note that every %FilterX% in the SQL statement MUST have at least 1 corresponding Jet filter):

D = Date

N = Number

S = String

G = GUID

B = Boolean

The entire FilterField (including the 1S, 2D, etc.) can be prefixed with a "+" or "-" to sort.  So, for example, you can use "+1S=FieldName" or "-1S=FieldName".

Please note that despite the fact that a sort may not correspond to any particular %FilterX% replacement, you must still prefix your soft field with a filter number, data type, and =.  Simply using +FieldName will not work.