PRB: Union Queries Do Not Inherit Properties of Base Table
  
PSS ID Number: Q113875
Article last modified on 03-13-1995
 
2.00
 
WINDOWS
 

---------------------------------------------------------------------
The information in this article applies to:
 
 - Microsoft Access version 2.0
---------------------------------------------------------------------
 
SYMPTOMS
========
 
A union query does not inherit the properties of the tables or queries
that the union query is based on.
 
CAUSE
=====
 
When you run a union query, the fields are displayed with the default
formats for the data types specified in the first table in the query.
 
RESOLUTION
==========
 
To work around this problem, create a select query based on the union
query, and then apply formatting in the select query.
 
MORE INFORMATION
================
 
Steps to Reproduce Behavior
---------------------------
 
 1. Open the sample database NWIND.MDB
 
 2. Open the Orders table in Design view.
 
 3. Change the format of the Order Date field to Long Date.
 
 4. Save and then close the Orders table.
 
 5. In the Database window, select the Orders table. Then, choose
    Copy from the Edit menu.
 
 6. From the Edit menu, choose Paste. In the Table Name box, type "Orders2"
    (without quotation marks). Make sure that the Structure And Data option
    button is selected, and then choose OK.
 
 7. Create a new, unbound query.
 
 8. From the Query menu, choose SQL Specific, then choose Union.
 
 9. In the Union Query window, enter the following SQL statement:
 
    NOTE: In the following sample SQL statement, an underscore (_) is used
    as a line-continuation character. Remove the underscore when re-
    creating this statement.
 
       SELECT [Ship Name], [Order Date] FROM Orders UNION ALL SELECT _
       [Ship Name], [Order Date] FROM Orders2;
 
10. Run the query by choosing the Datasheet View button on the toolbar.
    Note that the format for the Order Date field is Short Date, which
    is the default format for Date/Time fields.
 
To create a formatted select query based on the union query, follow these
steps:
 
11. Save the query as Union Without Format.
 
12. Create a new query based on the Union Without Format query.
 
13. Drag the Ship Name field from the field list to the query grid.
 
14. Enter the following expression in the second Field cell:
 
       Format([Order Date],"mmmm dd, yyyy")
 
15. Run the query by choosing the Run button on the toolbar. Note the
    format for the Order Date field.
 
REFERENCES
==========
 
For more information about union queries, search for "union query" using
the Microsoft Access Help menu.
 
Microsoft Access "User's Guide," version 2.0, Chapter 12, "Advanced
Queries"
 
Additional reference words: 2.00
KBCategory: kbusage
KBSubcategory: QryOthr
=============================================================================
Copyright Microsoft Corporation 1995.
