Negative Number in Query Incorrect When Returned to Worksheet

• Microsoft Query for Windows, version 1.0
• Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel, when you return data from a query in Microsoft Query, negative numbers are returned as positive numbers. For example, the value - 1 in Microsoft Query is returned as 65535, the value -2 is returned as 65534, and so on.

CAUSE

This error occurs when you return data from a column of field type TINYINT or SMALLINT (SQL table), or SHORT (Paradox table). The error occurs because Microsoft Excel assumes these INT data types are unsigned, but Microsoft Query assumes they are signed.

You cannot return negative numbers in Microsoft Excel from columns of type TINYINT, SMALLINT, or SHORT.

WORKAROUNDS

To return negative values from Microsoft Query to a Microsoft Excel worksheet, do either of the following:

• When you design the table, format the field as a type other than SQL_TINYINT, SQL_SMALLINT, or SHORT. For example, format the field as INT, FLOAT, or NUMBER type.

  -or-

• Use the Paste command to insert the data on the worksheet. Note that if you use this method, the information on the worksheet is not updated when the information in the query changes.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel version 5.0c for Windows.

MORE INFORMATION

In Microsoft Excel, you can link to data in an external database by choosing Get External Data from the Data menu. This command starts Microsoft Query, and allows you to use a data source to build a query. You can then choose the Return Data To Microsoft Excel command on the File menu in Microsoft Query to return the query data to your worksheet.