Behavior of query differs for byte data (199270)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q199270
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you create an update query that calculates values for a Numeric field whose FieldSize property is set to Byte, you may not get the results that you expect when the calculated value in the field is larger than what the Byte size data type can hold. If this happens, the query updates the value in the field to the calculated value minus 256.

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.
  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:
    1. In the Database window, click the Query tab, and then click OK.
    2. In the New Query box, click Design view, and then click OK.
    3. In the Show Table box, select Table1, click Add, and then click Close.
    4. Add Field1 to the query grid.
    5. On the Query menu, click Update Query.
    6. 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 that you run the query are as follows:
    QueryReturned Values
    One20, 30, 40
    Two40, 60, 80
    Three80, 120, 160
    Four160, 240, 64

REFERENCES

For more information about field sizes, click Microsoft Access Help on the Help menu, type fields, size in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MajorLast Reviewed:4/26/2004
Keywords:kbprb kbusage KB199270 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.