Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •