Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    FL
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query with No Values (Acc97)

    A textbox on a report has the source "=Count([qryField])". When the qry returns records, the total number of records shows up here. However, when the qry returns no values, it needs to read "0" so I can use it in a calculation but I get the "#Error". I've tried IIF, IIF(IsNull, Nz, IIF(Nz, <>, is null, is not null... aaaahhhh!!!
    Any ideas on the best way to get a zero for the value.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query with No Values (Acc97)

    Gee... it feels great not to be alone. I was about to post a very similar question last night!! Here's the solution I came up with. Cumbersome, but it works:


    =IIf(IsError([rptWorkOrderCostMechanicsSUB].[Report]![txtSumCost]),0,[rptWorkOrderCostMechanicsSUB].[Report]![txtSumCost])


    The subreport named rptWorkOrderCostMechanicsSUB contains a textbox named txtSumCost. If there are no records in the subform, it returns #Error#. The IIF function causes it to return zero.

    Hope this helps..
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query with No Values (Acc97)

    Is qryField a specific field in the report's recordset? Depending on what exactly you're trying to do, you can usually save yourself a lot of headaches by using a calculated true/false field in the underlying query and using Sum instead of Count in the report. Sum(Nz([qryField],0)) should work in that scenario.
    Charlotte

Posting Permissions

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