Filter Syntax In Your Jet Reports
Overview
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:
Filters
| Meaning | Symbol | Sample Expression | Records Displayed |
| Equal to | none |
377 BLUE |
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 | .. |
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" |
| One unknown character | ? | 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 |
@@ or ' ' |
@@A&B or '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 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) | @ | @location |
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 |
| @*co?* |
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) |
| <>n1&<>n2&<>n3 |
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.