Microsoft KB Archive/168875

= ACC: Behavior of Query Differs for Byte Data in Microsoft Access 95 and Microsoft Access 97 =

Article ID: 168875

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q168875



Moderate: Requires basic macro, coding, and interoperability skills.



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. The following behavior occurs 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.



Steps to Reproduce Behavior
 Start Microsoft Access 7.0 or 97.  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   Enter the following data in the first three rows:

     10, 15, 20  Close the table.</li> Create an update query based on Table1. To do so, follow these steps:

<ol> In the Database window, click the Query tab, and then click New.</li> In the New Query box, click Design view, and then click OK.</li> In the Show Table box, select Table1, click Add, and then click Close.</li> Add Field1 to the query grid.</li> On the Query menu, click Update Query.</li> In the Update To row, type [Field1] * 2.</li></ol> </li>  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:

<pre class="fixed_text">     Query        Values that are returned -              One         20, 30, 40 Two        40, 60, 80 Three      80, 120, 160 Four       160, 240, 64 </li></ol>

<div class="references_section">