By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,987 Members | 1,607 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,987 IT Pros & Developers. It's quick & easy.

How to format fields in UNION query?

Expert 5K+
P: 8,434
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.
Oct 22 '06 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Oct 22 '06 #2

Expert 5K+
P: 8,434
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.
Oct 23 '06 #3

P: 1
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?
Jan 18 '08 #4

Expert 5K+
P: 8,434
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. :(
Jan 21 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.