Currency Format Lost in Union Query

[Jill]

Thanks to John Spencer, I resolved one problem in my union query (Thank you,
John). But I have another problem. Some of my fields in my union query are
currencies and others percents. I see both formats are lost. I am sure it is
a union query problem, because when I ran an individual query, none of the
formats were lost. Is there any way I can keep currency and percent formats
without writing, FORMATCURRENCY, etc for each field?
Thank you.

 

[Allen Browne]

What data types are these fields?

The field in a UNION query will normally take on the data type of the field
in the first SELECT. So, if the field in the first SELECT statement of the
UNION is of type Currency, the output field would most likely be of type
Currency as well, unless something else is going on (e.g. if it's a
calculated expression, or if there are many Nulls, or ...)

Since you can't use Design view, you cannot set the Format property with the
Properties box, but it is possible to set it programmaticallly. You could
use the SetPropertyDAO() function from this page:
http://allenbrowne.com/AppPrintMgtCode.html#SetPropertyDAO
like this:
call SetPropertyDAO(CurrentDb.QueryDefs("MyQuery").Fields("MyField"),
"Format", dbText, "Currency")

In the end, the Format doesn't matter. It's purely a display thing, and you
generally don't expose the table or query itself to an end user. The more
important thing is the data type of the field, and setting the Format
property won't achieve that. To test the data type, use something like this
in the Immediate Window:
? CurrentDb.QueryDefs("MyQuery").Fields("MyField").type
The result will be one of the numbes from the DAO column on this page:
http://allenbrowne.com/ser-49.html
or you can use the FieldTypeName() function here:
http://allenbrowne.com/func-06.html

HTH