ACC2002: Make-Table Query Does Not Format Yes/No or Bit Fields Correctly (279150)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q279150
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SYMPTOMS

When you use a make-table query to create a new table, you are unable to change the format of Yes/No fields or Bit fields to display check boxes in Datasheet view.

RESOLUTION

There are two ways to resolve this issue.

For both Access databases (.mdb) and Access projects (.adp), you can change the display control to a check box as follows:
  1. Open the table in Design view.
  2. Click the Yes/No or the Bit field, and then click the Lookup tab.
  3. Change the Display Control property to Check Box.
  4. Open the table in Datasheet view. Note that the check boxes appear as expected.
For an Access database (.mdb) you can also correct this problem by changing the data type of the field to Number, and then change it back again to Yes/No as follows:
  1. Open the table in Design view.
  2. Change the data type of the Yes/No field to Number, and then save the table.
  3. Change the data type of the field back to Yes/No, and then save the table.
  4. Open the table in Datasheet view. Note that the check boxes now appear in the field.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Open the sample database, Northwind.mdb.
  2. Create a make-table query that is based on the Products table.
  3. Include all the fields from the Products table in the query.
  4. Open the query in Datasheet view. Note the check boxes in the Discontinued field.
  5. Run the query to create the new table.
  6. Open the new table in Datasheet view. Note that the Discontinued field now contains either 0 or -1.
  7. On the View menu, click Design View, and then change the Format property of the Discontinued field to Yes/No.
  8. Save the table, and then open it in Datasheet View. Note that there are still no check boxes in the Discontinued field.
  9. Switch the table to Design View, and then change the format to True/False or On/Off. Note that no matter which option you select, the check boxes do not appear.
Modification Type:MajorLast Reviewed:10/22/2003
Keywords:kbbug kbnofix KB279150
©2003 Microsoft Corporation. All rights reserved.