ACC95: Yes/No Field Causes Error in AutoLookup Query (142540)

RESOLUTION

There are two possible workarounds for using a Yes/No field in an AutoLookup query. Your choice depends on whether you need to modify the Yes/No field in the "one" side table.

Method 1

If you want to add records to the "many" side table, but only display (not modify) the Yes/No field from the "one" side table, you can work around the hidden default value for Yes/No fields by using an expression in the query grid. For example, you can replace a Yes/No field reference such as

   FieldName: MyYes/No
				

with the expression:

   FieldName: Expr1: [MyYes/No]+0
				

Because an expression is used to output the field instead of a direct reference, Microsoft Access does not try to set the value of the Yes/No field in the "one" side table when you add records to the AutoLookup query. A side-effect of this workaround is that you cannot modify the Yes/No field in the "one" side table.

Method 2

CAUTION: This method involves modifying the design of the "one" side table. If your application has multiple queries, forms, reports, and modules based on the table, this change may affect their behavior. To avoid potential side-effects caused by the design change, you should use Method 1 as the resolution for this issue.

If you need to modify the Yes/No field in the "one" side table and add records to the "many" side table, you can change the field's data type from Yes/No to Number. Then, you can set the field's FieldSize and Validation Rule properties to simulate the behavior of a Yes/No data type field. To do so, follow these steps:

1. Open your "one" side table in Design view.
2. Select the Yes/No field that you included in your AutoLookup query.
3. Change the field's properties to the following settings:

         Table: Sample
         -----------------------
         FieldName: SampleField
         DataType : Number
         FieldSize: Integer
         ValidationRule: 0 or -1
   					

4. Close the table and save the design changes. If you receive a "Data integrity rules have changed" message, click Yes to test your existing data against the new validation rule.

NOTE: If you have an existing form that has a check box, option button, or toggle button for entering data into the Yes/No field, the form control should continue to work properly after you change the field's DataType, FieldSize, and ValidationRule properties as described in step 3.