ACC: Behavior of Query Differs for Byte Data in MS Access 95/97

Last reviewed: July 15, 1997
Article ID: Q168875
The information in this article applies to:
  • Microsoft Access versions 7.0, 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

When you create an update query that calculates values for a numeric field, and you set the Field Size to Byte, you may not achieve the results you expect when the calculated value in the field is larger than what the Byte size data type can hold. The following behavior will occur if this happens.

In Microsoft Access 7.0 and 97

The query updates the value in the field to the calculated value minus 256.

In Microsoft Access 2.0

The query does not update the field, and you receive a conversion error message.

CAUSE

The Byte data type is used to store small, positive integers ranging from 0 to 255.

RESOLUTION

Use a different field size for your calculated field if you are going to be creating calculations that exceed 255.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Start Microsoft Access 7.0 or 97.

  2. Create a new table and save it as Table1. Add the following field to the table:

          Table: Table1
          --------------------
          Field name: Field1
    
             Data Type: Number
             Field size: Byte
    
    

  3. Enter the following data in the first three rows:

          10, 15, 20
    

  4. Close the table.

  5. Create an update query based on Table1. To do so, follow these steps:

        a. In the Database window, click the Query tab, and then click New.
    

        b. In the New Query box, click Design view, and then click OK.
    

        c. In the Show Table box, select Table1, click Add, and then click
           Close.
    

        d. Add Field1 to the query grid.
    

        e. On the Query menu, click Update Query.
    

        f. In the Update To row, type [Field1] * 2.
    

  6. Run the query four times. Click Yes to each confirmation message.

    Note that when the value that should be returned is greater than the Byte data type size limitation of 255, an incorrect value is returned to the field. In the above example, the values that are returned each time the query runs are as follows:

          Query        Values that are returned
          -----        ------------------------
           One         20, 30, 40
           Two         40, 60, 80
           Three       80, 120, 160
           Four        160, 240, 64
    
    

REFERENCES

For more information about field sizes, search the Help Index for "fields, size," or ask the Microsoft Access 97 Office Assistant.


Keywords : kbusage QryProp QryUpdat
Version : 97
Platform : WINDOWS
Hardware : x86
Issue type : kbprb
Resolution Type : Info_Provided


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: July 15, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.