Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet 365 Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Give Feedback

Special Characters in a Filter


Characters such as |><=.()& are considered special characters by Jet Essentials. If you need to include any of these characters as part of filter value then you need to precede the filter with "@@". The "@@" will pass the filter in as a literal string and be evaluated as intended. If you have two periods in a filter string (e.g., "A.M."), the filter includes special characters and needs to be prefixed by "@@" in most versions of Jet Essentials.

Note: In NAV the "@" itself is also considered to be a special character.  Unfortunately, NAV will not let you specify a filter with an "@" character in it which prevents Jet Essentials from applying this filter as well.  A filter with an "@" in it can be applied in a Universal Data Source.

For example, if you need to filter by a customer named "John(Houston)" you will need to place the "@@" in front of "John(Houston)". The resulting formula would look like the following:

=NL ("Rows","Customers","CustomerID","ContactName","@@John(Houston)")

Since the name John(Houston) would often be located in an adjacent cell, below is the equivalent formula using a cell reference:

=NL("Rows","Customers","CustomerID","ContactName","@@"&C3)

If you need to combine several filters together that all include special characters, you need to enclose each filter item in single quotes (') instead of using the "@@" prefix.

To filter by customers named "John" or "Ben", you need to use the logical OR (a vertical bar |) operator. Since there are no special characters, your function will look like the following:

=NL("Rows","Customers","CompanyName","CompanyName","John|Ben")

If, however, the customers names were "John A.M." or "Ben & Jerrys", you would need to enclose each name in single quotes (').

The single quotes around each name are required because applying the "@@" prefix to the entire filter would cause the | to evaluate as a part of the filter instead of being evaluated as an operator.  Meaning your filter would be "John A.M|Ben & Jerrys", instead of "John A.M" or "Ben & Jerrys".  The following demonstrates how to filter by multiple values with special characters:

=NL("Rows","Customers","CustomerID","CompanyName","'John A.M'|'Ben & Jerrys'")

{please note the single quotes around each of the filter values:  ... ," ' John A.M ' | ' Ben & Jerrys ' " }

Again, these names could be coming from cell references, so the equivalent formula with cell references is below:

=NL("Rows","Customers","CustomerID","CompanyName","'"&C3&"'|'"&C4&"'")

Was this article helpful?
2 out of 2 found this helpful
Have more questions? Submit a request

Comments