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

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

     

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

Comments