Currency Formatting Union Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JimS - 31 Oct 2007 15:57 GMT

I have a union query as follows:
(select * from qrysumofstdhrs) union (select * from qrysumofothrs) UNION
(select * from qrysumofdthrs)
ORDER BY vendorid, workername, workdate;

Several of the fields should be seen as currency format. I've coded them as
such in the underlying queries, and the underlying queries show them as
currency. The union query respects that format only sometimes. One field
comes through as currency like a champ. Another does not. How can I force the
union query to see them as currency? I'm willing to list the fields I need
rather than "select *" if need be.

Signature

Jim

Reply to this Message

Jerry Whittle - 31 Oct 2007 16:47 GMT

Try something like:

SELECT vendorid,
workername,
workdate,
CCur([TheCurrencyField])
FROM
(SELECT vendorid,
workername,
workdate,
[TheCurrencyField]
FROM qrysumofstdhrs
UNION
SELECT vendorid,
workername,
workdate,
[TheCurrencyField]
FROM qrysumofothrs
UNION
SELECT vendorid,
workername,
workdate,
[TheCurrencyField]
FROM qrysumofdthrs)
ORDER BY vendorid, workername, workdate;

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I have a union query as follows:
> (select * from qrysumofstdhrs) union (select * from qrysumofothrs) UNION
[quoted text clipped - 7 lines]
> union query to see them as currency? I'm willing to list the fields I need
> rather than "select *" if need be.

Reply to this Message