Jet Basics - Filter Syntax

377
BLUE

The number: 377

Those with the BLUE code (e.g., BLUE warehouse code)

Not

equal to

All numbers except zero.

Note that <> can only be used in conjunction with other wild cards such as * when using a SQL database (not NAV local or NAV Server database)

Numbers that are less than 2000 AND greater than 1000

The & sign must be used with a range because no single record can have two values.  An example of an invalid use of & is A&B - because no records can be equal to A *AND* equal to B. An example of a valid use of & is A*&*B, which means 'find all records that start with A and end with B'.

Greater than or Equal to

Less than or Equal to

1100..2100

..2500

..12/31/2019

8..

Number between 1100 and 2100 (inclusive)

All number up to and including 2500

All dates up to and including December 31, 2019

Everything 8 or higher

Indefinite number of unknown characters

*Co*

*Co

Co*

Text that contains "Co"

Text than ends with "Co"

Text that begins with "Co"

Hans?n

Text such as 'Hansen' or 'Hanson'

Calculate first

30|(>=10&<=20)

The number 30 OR with a number between 10 through 20 (inclusive)

Ignore special characters 

@@A&B

Those equal to "A&B" - the & is part of the value, instead of being interpreted as the AND operator (listed above)

"@@"&C3

or

""&C3&""

or

""&C3&"|"&C4&""

where C3 and C4 could contain blank or special characters such as: "John(Huston)", "Ben & Jerry's"

Use "@@"& <Cell Reference> if the cell reference could be blank and you want to filter for blank field values.

Use the ' character in double quotes added before and after a cell reference to always have a safe filter from the cell reference.

The two methods listed above are equivalent for a single filter.  If you want to combine two cells with filters in them, you must use the technique shown in the bottom example.

Ignore case

(upper or lower)

@location

Text such as "Location", "LOCATION", "locatioN", etc.

Include records with a number less than or equal to 1299 OR a number equal to or greater than 1400

(i.e., all numbers except 1300 through 1399)

Text containing co, CO, Co, cO (such as "cot","cope","incorporated")

CO, cO, Co, or co must be present, followed by at least one character, but there can be an indefinite number of characters before and after these, and case in unimportant. 

Used to eliminate more than one value.

Note: In Dynamics NAV, Do not use multiple <>& with wildcards, as the results may be inconsistent.