Knowledge of correct filter construction is not necessary as the filter is automatically generated as you click on the relevant buttons, points, or grid cells. However, if you wish to manually alter the filter knowledge of the correct formats will prevent receiving error messages.

Formatting

When double clicking on a value it is automatically produced in the appropriate format for the data type you are querying. For example

•text will be enclosed by a single quote ( eg. 'text');

•dates will be surrounded by the date identifier ( eg #24 Jan 02#);

•numbers will be unformatted.

Values are not case sensitive.

Fields which contain spaces or other reserved characters are enclosed in square brackets:

Operators

< , >, <=, >=

You can use greater than/less than operators to select string values based on sorting order. For example, this query will select all the concentrations greater than 1000 (the concentration is typically stored separately to the units):

Concentration > 1000

<>

This operator (<>) means not equal to. For example to select all chemical results except metals.

Method_Type <> ‘Metals’ (*the identifier Metals or Met, or similar will depend on your laboratory)

This operator should be used with care as any blank (Null) values will also be excluded. In the above example if any results had a blank Method_Type they would also be excluded (this is specified by Access databases and cannot be overridden). A more precise use (but less intuitive) is:

Method_Type <> ‘Metals’ or Method_Type Is Null

IS NULL, IS NOT NULL

You can use the IS NULL operator to select records that have null values for the specified field. For example, to find Locations whose elevation hasn't been entered, you can use:

Elevation IS NULL

Conversely use of Is Not Null will return those records which have a value.

In

You can use the In operator to select multiple values for a field. For example, to find multiple Locations, you can use:

LocCode In(BH1,BH2,BH3)

Like

Wildcards can be used with the "Like" operator.

'?' indicates one character.

'%' indicates any number of characters.

For example, this query will select all locations starting in 'BH'

LocCode like('BH%')

If you use a wildcard character in a string with the = operator, the character is treated as part of the string, not as a wildcard.

Not

The Not operator is not included as a button, but “Not” can be written prior to any other operator or expression.

Use the NOT operator at the beginning of an expression to find features or records that don't match the specified expression, alternatively use the <> operator.

NOT Geologic_Unit = 'Alluvium'

Geologic_Unit Not Like(‘Alluv%’)

Wildcards

Wildcards can be used with the "Like" operator.

'?' indicates one character.

'%' indicates any number of characters. (note that a '*' as used in the main Access Application doesn't work, a '%' must be used.)

For example, this query will select all locations starting in 'BH'

LocCode like('BH%')

If you use a wildcard character in a string with the = operator, the character is treated as part of the string, not as a wildcard.

Combining expressions

Complex queries can be built by combining expressions together with the AND and OR operators. For example, to select all locations starting in 'BH' with concentrations greater than 1000, use this query:

LocCode like('BH%') AND Concentration > 1000

When you use the OR operator, at least one expression of the two expressions separated by the OR operator must be true for the record to be selected

Calculations

Calculations can be included in queries using these mathematical operators: + - * /

Calculations can be between fields and numbers. For example:

Concentration >= ActionLimit * 10

Or between fields.

Concentration/ActionLimit >= 10

Order of Operations

Queries are evaluated in the native format of Access databases. ie;

Normal mathematical order of operations apply (evaluate * and / before + and -)

The And expression is evaluated before OR.

Expressions enclosed in parentheses are evaluated first

For example, the first query will evaluate (Geologic_Unit <> 'Alluvium' Or LocCode Like(BH%)) first then exclude those records whose concentration is not > 1000:

Concentration > 1000 AND (Geologic_Unit <> 'Alluvium' Or LocCode Like(BH%))

The query below will evaluate Concentration > 1000 AND Geologic_Unit <> 'Alluvium' first then include all records where the LocCode is like (BH%):

Concentration > 1000 AND Geologic_Unit <> 'Alluvium' Or LocCode Like(BH%)

Filtering SQL