1. Total Query (A2K SP-3)

﻿I have a total query, qrySumTotalRoomReceivable as follows:

SELECT Sum(qryARPaidRoyalty.TotalRoom) AS SumOfTotalRoom,
qryARPaidRoyalty.AccountReceivable
FROM qryARPaidRoyalty
GROUP BY qryARPaidRoyalty.AccountReceivable
HAVING (((qryARPaidRoyalty.AccountReceivable)=-1));

What I need to do is to have [TotalRoom] from qryARPaid Royalty return a zero, if there are no
records that meet the select criteria.

Any assistance would be appreciated.

2. Re: Total Query (A2K SP-3)

You will have to set the Total option for AccountReceivable to Where instead of Group By. Then, you can use Nz to return 0 if there are no records meeting the criteria:

SELECT Nz(Sum([TotalRoom]),0) AS SumOfTotalRoom
FROM qryARPaidRoyalty
WHERE (((qryARPaidRoyalty.AccountReceivable)=-1));

3. Re: Total Query (A2K SP-3)

Many Thanks. Can the Sum be formated as Currency? The format property on the Sum of TotalRoom field doesn't convert to currency format.

Tom

4. Re: Total Query (A2K SP-3)

If you are using the query as a recordsource for a report, set the formatting in the report, otherwise, I'm not sure what you mean. Setting the currency format for a Sum field in a totals query works on my A2k SR-3 as far as display is concerned. If you're exporting, then you lose formatting, which is only for display. In that case, you would have to use the Format function in the query or use the CCur function, which will retain 4 decimal places.

5. Re: Total Query (A2K SP-3)

One way is to force the result to currency:

SELECT CCur(Nz(Sum([TotalRoom]),0)) AS SumOfTotalRoom
FROM ...

The result will probably be displayed OK, and you should be able to set the format property of the field.

Another way is to put the format in the field definition:

SELECT Format(Nz(Sum([TotalRoom]),0),"Currency") As SumOfTotalRoom
FROM ...

The result of the Format function is text; you can't use it in calculations.

6. Re: Total Query (A2K SP-3)

Many Thanks.

The first method:
SELECT CCur(Nz(Sum([TotalRoom]),0)) AS SumOfTotalRoom
FROM
works perfectly. I do need to use it in a later calculation.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•