union query

[Samual]

Ive got a problem concerning union queries. I have a financial statement report based upon a union query which combines 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.

 

[Rich]

Set the textbox format to Currency

 

[raskew]

Try something like this. Note: Rename your date field as Date is a reserved work in Access and may cause complications when used as a field name. In my test tables, both [Income] and [Expenditure] were formatted as Currency.

SELECT transdate, "Income" AS Type, [income]*1 AS Amount, details
FROM tblIncome
Union all
SELECT transdate, "Expenditure" as Type, [expenditure] *-1 AS Amount, details
from tblExpenditure
ORDER BY transdate;

 

[Samual]

THANKS A MILLION...the *-1 did the trick...i just can't believe how easy it was but it just didn't click
Once agains thasnks alot