How to format fields in UNION query?

[Killer42]

Hi all.

First off, this relates to Access 2003.

I have created queries in a couple of databases, in which I have set the Format property for some calculated fields to show hours, minutes and seconds. Now I want to create a separate database to use as the front-end, separate from the data. In this, I have a UNION query which combines the identical tables in various databases.

It works well, but the calculated fields come out as a long decimal number, which is no use. How can I set the format for the calculated fields, in the UNION query? In the other cases I just brought up the properties for the field in the query designer, and set Format appropriately. This doesn't have any visible effect on the SQL, so I don't know what to do now. UNIONs don't allow one to use the designer, only the SQL view.

[MMcCarthy]

SELECT Format(NumberField,"0.00"), Field2, Field3
FROM Table1
UNION
SELECT Format(NumberField,"0.00"), Field2, Field3
FROM Table2
UNION
SELECT Format(NumberField,"0.00"), Field2, Field3
FROM Table3

[Killer42]

SELECT Format(NumberField,"0.00"), Field2, Field3
FROM Table1
UNION
SELECT Format(NumberField,"0.00"), Field2, Field3
FROM Table2
UNION
SELECT Format(NumberField,"0.00"), Field2, Field3
FROM Table3

Thanks for that, mmccarthy.

What I've done this morning is to create a second query based on the UNION query, and set the Format property for the fields in that. It appears to work.

When I have time, I'll try your method, as I'd prefer not to add yet another level. One of these days I'll have to find time to do a comparison of the performance. Any idea whether there's much difference?

Hm... On further consideration, I'm not sure whether the Format() function will provide what I need in this case. you see, I'm using a format of "hh:nn:ss" to format an amount of time. Unfortunately this clocks over after 24 hours, so I can't really believe what I see there. I tend to copy/paste interesting results into Excel, and reformat there. Since it's a number, this currently works. But if it's a formatted string, it might not. Anyway, I'll let you know how it all works out.

Ta.

[jbrink]

I have the same issue with union all.....This format function in the query works on a single select statement but once you create a union with another table using the same format function the formatting is not implemented... How can you get the formatting to stick on the output?

[Killer42]

I have the same issue with union all.....This format function in the query works on a single select statement but once you create a union with another table using the same format function the formatting is not implemented... How can you get the formatting to stick on the output?

I can't recall whether Mary's suggestion worked or not. Looking over an old database here, I see that I do have a query which uses my method. That is, the union query comines not tables, but queries which already include the formatting. It does appear to work.

For example..

Table1 includes fields Time1, Time2
Table2 includes fields Time1, Time2
and so on.

Query1 says Select hms([Time2]-[Time1]) AS ElapsedTimed From Table1
Query2 says Select hms([Time2]-[Time1]) AS ElapsedTimed From Table2
etc.

Then the biggy says

SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2
blah blah blah.

Note, the hms() function just returns the passed date/time value in the format I wanted.

This approach does work. But of course the usual rule applies - don't "where out" your functions. :) If you use a WHERE clause on the ElapsedTime field, you'll get (in my case) the three day wait that you deserve. Because Access will have to read all the millions of records and pass their values through the function, to determine which records to read. :(