Microsoft KB Archive/113875

PRB: Union Queries Do Not Inherit Properties of Base Table

PSS ID Number: Q113875 Article last modified on 03-13-1995

2.00

WINDOWS

= 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
  Open the sample database NWIND.MDB   Open the Orders table in Design view.   Change the format of the Order Date field to Long Date.   Save and then close the Orders table.   In the Database window, select the Orders table. Then, choose Copy from the Edit menu.   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.   Create a new, unbound query.   From the Query menu, choose SQL Specific, then choose Union. </li>  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; </li>  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. </li></ol>

To create a formatted select query based on the union query, follow these steps:

<ol start="11" style="list-style-type: decimal;">  Save the query as Union Without Format. </li>  Create a new query based on the Union Without Format query. </li>  Drag the Ship Name field from the field list to the query grid. </li>  Enter the following expression in the second Field cell: Format([Order Date],“mmmm dd, yyyy”) </li>  Run the query by choosing the Run button on the toolbar. Note the format for the Order Date field. </li></ol>

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