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

Filter Syntax In Your Jet Reports

Related Articles...


When creating reports, it is important to have an 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)

Not equal to <> <>0

All numbers except zero.

Note: When using Dynamics NAV, <> can only  be used in conjunction with other wild cards such as * when using a SQL database (not a local NAV 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 single quote character  '   enclosed within 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
..1299|1400.. 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, using multiple <>& with wildcards may return inconsistent results.  This typically does not apply when using SQL databases, but definitely can be an issue with local NAV or NAV server databases.


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?
1 out of 1 found this helpful