UNION Queries

[Ryan0001]

Ive got a problem concerning union queries. I have a financial statement report based upon a union query which combine data from 4 tables. Its quite complicated, with a lot of calculations and parameters, but the basic structure is as follows:
SELECT [Date], [Income], "" As Expenditure, [Details]
FROM [Income]
UNION SELECT [Date], "", [Expenditure] [Details]
FROM [Expenditure]
ORDER BY [Date];
The problem is that I want the expenditure to appear as a negative currency so that I can easily calculate the profit on the report. Instead what happened was that the income appeared as a currency, and the expenditure appeared as a negative number not currency. I tried many things including the following:
1- putting "-$" &""& before [Expenditure]. When I did so access no longer recognised it as a number and so the calculating of the profit was not worked out.
2- I also created a new select query, and put in all the fields from the union query in it. I the tried changing the format of the expenditure to currency but this didn’t work either.
So all I want is to have both expenditure and income as a currency and the expenditure as a negative number..

Many Many thanks to anyone who can help.

 

[ToeShot]

I will assume the query will be put into a report. In the report you can format the Expenditure field to be currency.

Dave
ToeShot@Hotmail.com
Today Is Tomorrows Yesterday. So Why Wait

 

[raskew]

Come on Ryan or Samual or whatever your name is. You already got it here:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=48277