Prev: version
Next: Translate if statement in query
From: Jill on 27 May 2010 17:26
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.
From: Allen Browne on 27 May 2010 22:42
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Jill" <Jill(a)discussions.microsoft.com> wrote in message
news:793226F2-D606-4CC3-9297-13D47FCDF2CF(a)microsoft.com...
> 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.

 | 
Pages: 1
Prev: version
Next: Translate if statement in query