My Update Query Loses Currency Format

[60myles]

I have written the following Query -

SELECT Transactions.TransactionNumber, Transactions.TransactionDate, Transactions.TransactionDescription, Transactions.WithdrawalAmount, Transactions.DepositAmount
FROM Transactions
WHERE (((Transactions.TransactionDate) Between [Forms]![Report Date Range]![Beginning Report Date] And [Forms]![Report Date Range]![Ending Report Date]))
UNION SELECT DISTINCTROW " " AS TransactionNumber, [Forms]![Report Date Range]![Ending Report Date] AS TransactionDate, "Totals" AS TransactionDescription, SUM(Transactions.WithdrawalAmount) AS WithdrawalAmount, SUM(Transactions.DepositAmount) AS DepositAmount
FROM Transactions
WHERE (((Transactions.TransactionDate) Between [Forms]![Report Date Range]![Beginning Report Date] And [Forms]![Report Date Range]![Ending Report Date]))
UNION SELECT " " AS TransactionNumber, [Forms]![Report Date Range]![Beginning Report Date] AS TransactionDate, "Opening Balance" AS TransactionDescription, "" AS WithdrawalAmount, SUM(Transactions.DepositAmount) - SUM(Transactions.WithdrawalAmount) AS DepositAmount
FROM Transactions
WHERE (((Transactions.TransactionDate) Between [Forms]![Report Date Range]![Beginning Report Date] And [Forms]![Report Date Range]![Beginning Report Date]))
UNION SELECT " " AS TransactionNumber, [Forms]![Report Date Range]![Ending Report Date] AS TransactionDate, "Ending Balance" AS TransactionDescription , "" AS WithdrawalAmount, SUM(Transactions.DepositAmount) - SUM(Transactions.WithdrawalAmount) AS DepositlAmount
FROM Transactions
WHERE (((Transactions.TransactionDate) Between #09/01/01# And [Forms]![Report Date Range]![Ending Report Date]));

The problem is that I believe the <&quot;&quot; AS WithdrawalAmount> part causes the Currency format of the field to be lost so that the printed report prints the DepositAmounts OK ($##.##), but the whole WithdrawalAmount in the report just prints as an unformatted number field. How do I force the <&quot;&quot;> to Format Currency in a query?

This is probably a dumb question but I can't seem to find an answer in any &quot;help&quot; files or manuals I have.

 

[60myles]

Well, I figured it out. I made a one record, one (empty) field, currency format, table, and rewrote my query as follows:

SELECT Transactions.TransactionID, Transactions.TransactionNumber, Transactions.TransactionDate, Transactions.TransactionDescription, Transactions.WithdrawalAmount, Transactions.DepositAmount
FROM Transactions
WHERE (((Transactions.TransactionDate) Between [Forms]![Report Date Range]![Beginning Report Date] And [Forms]![Report Date Range]![Ending Report Date]))
UNION SELECT &quot;0&quot; as TransactionID,&quot;&quot; AS TransactionNumber, [Forms]![Report Date Range]![Beginning Report Date] AS TransactionDate, &quot;Opening Balance&quot; AS TransactionDescription,(SELECT TOP 1 [DepositAmt1].[DepositAmt] FROM [DepositAmt1]) AS WithdrawalAmount, SUM(Transactions.DepositAmount) - SUM(Transactions.WithdrawalAmount) AS DepositAmount
FROM Transactions
WHERE (((Transactions.TransactionDate) Between #09/01/01# And [Forms]![Report Date Range]![Beginning Report Date]))
UNION SELECT &quot;99998&quot; AS TransactionID,&quot; &quot; AS TransactionNumber, [Forms]![Report Date Range]![Ending Report Date] AS TransactionDate, &quot;Totals&quot; AS TransactionDescription, SUM(Transactions.WithdrawalAmount) AS WithdrawalAmount, SUM(Transactions.DepositAmount) AS DepositAmount
FROM Transactions
WHERE (((Transactions.TransactionDate) Between [Forms]![Report Date Range]![Beginning Report Date] And [Forms]![Report Date Range]![Ending Report Date]))
UNION SELECT &quot;99999&quot; AS TransactionID,&quot; &quot; AS TransactionNumber, [Forms]![Report Date Range]![Ending Report Date] AS TransactionDate, &quot;Ending Balance&quot; AS TransactionDescription ,(SELECT TOP 1 [DepositAmt1].[DepositAmt] FROM [DepositAmt1]) AS WithdrawalAmount, SUM(Transactions.DepositAmount) - SUM(Transactions.WithdrawalAmount) AS DepositlAmount
FROM Transactions
WHERE (((Transactions.TransactionDate) Between #09/01/01# And [Forms]![Report Date Range]![Ending Report Date]))
ORDER BY TransactionID;

And it works fine - corrected all the other errors in the original as well!