Access Data Source Error Entering Record with Long Data Type (123329)

SYMPTOMS

In Microsoft Query, when you add a record to a query that you created using a Microsoft Access version 1.1 data source, you may receive the following error message:

Error converting to numeric type: Parameter #3

If you add a record to a query that you created using a Microsoft Access version 2.0 data source, you receive the following error message:

Numeric value out of range

Note that when you choose OK in the dialog box that contains either of the error messages above, you receive the following error message:

Update Failed. Continue editing record?

CAUSE

This behavior occurs if you add a record that contains a field of Long data type, and the value that you entered in the field exceeds the allowable range for a field of Long data type.

Normally, when you enter a value in a field, and you exceed the number of characters allowed for that field, you receive the following error message:

<value> exceeds the numerical range of this column

Additionally, you receive the error message immediately after entering the value, and the value is not added to the field. However, when you add a value to a field of Long type, the value is entered, even if it exceeds the allowable range, and you receive either of the above error messages when you add the record.

This behavior occurs because the data source driver cannot detect the value overflow until the record is actually entered.

WORKAROUND

To avoid receiving these error messages, enter values that are within the acceptable range for the data type of the field in which you are entering data. Use the table provided below to determine the allowable range for each number data type in a Microsoft Access database.

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

The following table shows the data types used in Microsoft Access, the data types used to create tables in Microsoft Query using version 1.1 or 2.0 of the Microsoft Access data source, and the range of values allowed for each data type.

                       Data type in
Microsoft Access       Microsoft Query                Range of
Data Type              (Microsoft Access 1.1/2.0)     Values Allowed
-------------------------------------------------------------------------

Number(Single)         SingleFloat/Float4     -3.402823E38 to 3.402823E38

Number(Double)         DoubleFloat/Float8      1.79769313486232E308
                                                           to
                                               1.79769313486232E308

Number(Byte)           UnsignedByte/Byte                 0 to 255

Number(Integer)        Short/Short                 -32,768 to 32,767

Number(Long Integer)   Long/Long            -2,147,483,648 to 2,147,483,647

REFERENCES

For more information about Data Types (Advanced), choose the Search button in the ODBC Microsoft Access Driver Help and type:

data types