Sign Up for Training |
insightsoftware Company Site
Submit a Request
Become a Jet Insider
Give Feedback

Jet Basics - Filter Syntax

Related Articles...


When creating reports, it is important to have a basic understanding of how to apply filters to your queries so as to get exactly the data you want from your database.

This reference provides the methods for applying various filter types ("not equal to", "greater than", "and", "or", etc.) to your Jet functions: 


Meaning Symbol Sample Expression Records Displayed
Equal to none


The number: 377

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


equal to

<> <>0

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)

And & <2000&>1000

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'.

Or | 1200|1300 Those with number 1200 OR 1300
Greater than > >1200 Numbers greater than 1200

Greater than or Equal to

>= >=1200 Numbers greater than or equal to 1200
Less than < <1200 Numbers less than 1200

Less than or Equal to

<= <=1200 Numbers less than or equal to 1200
Interval ..





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





Text that contains "Co"

Text than ends with "Co"

Text that begins with "Co"

One unknown character ?


Text such as 'Hansen' or 'Hanson'

Calculate first



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

Ignore special characters 



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







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)



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


You can also combine the various filter expressions as in the following table.

Example Results
5999|8100..8490 Include all records with the number 5999 OR a number from 8100 through 8490

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)

>50&<100 Include records with numbers that are greater than 50 AND less than 100 (i.e., 51 through 99)
*C*&*D* Text containing both the upper case letter C AND the upper case letter D

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. 

A*..C* Include text that starts with any upper case letter between A and C (inclusive - A, B, or C)

Used to eliminate more than one value.

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


Note: It is important that you enter only meaningful filters. For example, it is possible to specify an interval that does not exist, and the Jet Excel add-in cannot check this for you.

Was this article helpful?
0 out of 0 found this helpful