## PRB: Union Queries Ignore Field Formatting

Article ID: Q120973
Creation Date: 23-SEP-1994
Revision Date: 01-DEC-1996

The information in this article applies to:

- Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you run a union query using tables containing formatted data, such as Yes/No fields or formatted numerical data, the data in the resulting recordset is not formatted as it is in the original tables.

CAUSE

Union queries ignore formatting information for fields.

RESOLUTION

Use the Format property in a report, form, or select query based on the union query to restore the data's formatting.

MORE INFORMATION

Steps to Reproduce Behavior

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].

- 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.

  REFERENCES

  For more information about union queries, search the Help Index for "union queries."

> THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

©1997 Microsoft Corporation. All rights reserved. Legal Notices.
KBCategory: kbusage
KBSubcategory: QryUnion
Additional reference words: 2.00 7.00 8.00 97 sql-specific