How Numeric Values are Displayed

Warning: this is a technical topic for advanced users only.

We are often asked how numeric (in particular decimal) values are displayed in AQT, and how to change the way they are displayed. This is a surprisingly complicated area - we describe here how it works.

The are two parts to this discussion:

• how numeric values are retrieved from the database by AQT
• how numeric are displayed in the grid

How Numeric Values are retrieved from the Database

When data is retrieved from the database into AQT, AQT supplies program variables for the data to be placed into (by the ODBC Driver). For numeric variables, AQT has a choice of retrieving them into either:

• numeric program variables (in which case they "go into" AQT as numbers)
• string program variables (in which case they "go into" AQT as strings)

Most numeric values go into AQT as numbers. Except:

• decimal column types. These always go into AQT as strings. This is because PCs do not have a numeric data type that corresponds to a decimal column type. The only way to display the decimal value accurately is to retrieve them as strings.
• if Options > Technical Parameters > Bind all cols to strings is specified. In this case all columns go into AQT as strings.

When AQT retrieves the decimal value as a string, it is the ODBC Driver that decides how the decimal value is to be formatted as a string. So, it is the ODBC Driver that decides whether to display the decimal value as (for instance) 12.345 or +1.2345E+1.

Some ODBC Drivers have options that specify how it is to format the decimal values. For instance SQL Server has the setting Use regional settings when outputing currency, number, dates and times. When this option is selected, SQL Server will use the Control Panel regional settings for formatting the decimal values.

How Numeric Values are displayed in the Grid

Once AQT has retrieved the numeric value, it will place the value in the display grid. There are two ways in which the grid works. This is governed by Options > Display Options > Use Grid formatting for Numeric Columns.

• when this option is not selected, the grid columns will be defined as string columns. The numeric values will be displayed as AQT has retrieved them from the database.
• when this option is selected, the grid columns will be defined as numeric columns. The grid will then choose how it will display the numeric value. By default, this will be as per Control Panel > Regional Settings. This may be different to the way AQT has retrieved the values from the database.

  You can change the way numeric values are displayed in the grid with View > Format Data (from the Data Display window).

Exporting Data

When data is exported, numeric values are written as they have been retrieved from the database.

Decimal Separator is Comma

Databases always return decimal values with a dot as the decimal separator, even in cases where this has been set as a comma in the Regional Settings.

AQT will detect that the Regional Settings have specified a comma, and will replace the dot with a comma in the decimal values that are retrieved from the database.

If you have specified Use Grid formatting for Numeric Columns, the grid will also be aware that the decimal separator is a comma. There has been the odd problem whereby AQT hasn't replaced the dot with a comma when it should have. When this happens, the grid interprets the dot as a grouping-indicator (thousands separator) and has displayed the decimal value as 1000 times the correct value. If you suspect this might be happening to you, switch off Use Grid formatting for Numeric Columns.