PRB: Union Queries Ignore Field Formatting

The information in this article applies to:

• Microsoft Access versions 2.0, 7.0, 97

1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).
2. In the Database window, select the Products table. Click Copy on the Edit menu, and then click Paste on the Edit menu. Type Prod2 in the Table Name box, and then click the Structure Only option. Click OK.
3. Open the Prod2 table in Datasheet view and add two or three records, using any data you want. In Microsoft Access 7.0 and 97, note that the data in the Discontinued field is formatted with the Lookup Display Control set to Check Box. This field contains a check for Yes and a blank check box for No.
4. Create a new query. Do not add any tables.
5. On the Query menu, point to SQL Specific, and then click Union.
6. Type the following SQL statement in the Union Query window:

   NOTE: In the following SQL statement, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when typing this statement.

         SELECT DISTINCTROW Products.[ProductID], Products.[_
            ProductName], Products.Discontinued, "Products"
         FROM Products
         UNION ALL SELECT DISTINCTROW Prod2.[ProductID], Prod2.[_
            ProductName], Prod2.Discontinued, "Prod2"
         FROM Prod2;
   

   NOTE: In version 2.0, type a space in [Product ID] and in [Product Name].

7. Run the query. Note that the data in the Discontinued field is displayed as -1 or 0, rather than as Yes/No or check boxes.