NAV Code and Number fields with Special Characters

Characters used in NAV filters (such as the ampersand "&", pipe "|", and range indicator "..") should not be used in Code or No. fields. In addition, you should not use blank Codes or No.s.  Using blanks or special characters in your Code or No. fields will cause problems with reporting and filtering in both NAV and Jet Reports.  Fortunately, it is rare to find blanks or special characters in Code or No. fields.

When field values contain filter characters such as "&", you must put the field values in single quotes to use them in a filter.  For example, if customer IDs have embedded "&"s you can filter on the customer ID as follows.

B2: =NL ("Rows","Customer","No.")

B3: =NL(,"Customer","Name","No.","'"&B2&"'")

The customer number is in a formula that surrounds it with single quotes: "'"&B2&"'".  This causes NAV to consider any embedded special characters as part of the field value.