Use Special Characters in a Filter

Help Topics...

Overview

Certain characters have specific meanings in queries and are considered to be special characters

e.g.,

|   >   <   =    .  (   )   &

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 the Jet Excel add-in.

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

Examples

1. Single Filter

   If your were to filter by the customer named "John(Houston)" you would need to place the "@@" in front of "John(Houston)".  The resulting formula would look like:

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

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

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

2. Multiple Filters

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

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

   =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&"'")