XL97: "Numeric Value Out of Range" Error in Parameter Query

• Microsoft Excel 97 for Windows
• Microsoft Query 97 for Windows

SYMPTOMS

When you run a parameter query, one of the following error messages appears.

In Microsoft Query

   Numeric value out of range

Returning Data to Microsoft Excel

   [Microsoft] [ODBC SQL Server Driver]Numeric Value out of range

CAUSE

In Microsoft Query

This problem occurs when you do the following:

• You run a query against a SQL server.

      -and-
  

• The fields in the query use any of the following data types:

        Decimal
        Numeric
  

      -and-
  

• The value you enter for the parameter is greater than 32,767 or less than -32,768.

In Microsoft Excel

This problem occurs when you do the following:

• You return data from a SQL server and you use multiple parameter queries.

      -and-
  

• The parameters are fields of different data types.

WORKAROUND

If the field type is integer (numeric), limit data to only integer values between -32,768 and 32,767. For more information about field types, see the table in the "More Information" section in this article.

If you use multiple parameters and return data to Microsoft Excel, change the order of the parameter queries. Place the parameter that refers to a numeric field first.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Data types are limited in the values they can store. The data type, storage size, and range limit for each type are listed in the following table.

   Data type   Storage size   Range limit
   -----------------------------------------------------------------

   Numeric       2 bytes      -32,768 through 32,767
   (integer)

   Currency      8 bytes      -922,337,203,685,477.5808
                              through 922,337,203,685,477.5807

   Decimal      14 bytes      +/-79,228,162,514,,264,337,593,950,335
                              through +/-7.9228162514264337593543950335

   parameter