Trim Blank Spaces in Database Fields

Some databases, including Dynamics GP, store text data in fixed length fields.

One example of a fixed length text field is the Customer Name in Dynamics GP.  GP defines this field as 25 characters and if you put a short name like Bob into the customer entry screen, GP will store Bob in the database as Bob followed by 22 spaces.

In Dynamics GP, and most other databases that store their text fields at a fixed length, the application interface will conceal the blank spaces from you.  If you filter for "Bob", GP will find the customer "Bob " even though there are 22 spaces after the name and you did not enter "Bob*" as the filter.  GP will also display this username as "Bob", trimming the trailing spaces so you never see them.

The Jet Excel add-in can provide an equivalent translation between text values and filters if you activate Trim trailing spaces from text fields , located on the Advanced tab of the Data Source Settings

We recommend that Dynamics GP users activate this option.  It may also be useful for other database types depending on how the database application represents empty space.  This feature is not available when using ODBC drivers unless the Data Source is of type Dynamics GP. Enabling this option may incur a slight performance hit to the run time of the report, depending on the size of the database and number of text values that are affected.

This feature does not apply to Dynamics NAV users.