Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57

    Unanswered: Query Help Please

    Hello all.

    I have a query where I am trying to find the number of hogs that meet certain characteristics. On occasion, however, the query comes up with none, and instead of having 0 as an output, it has nothing, not even a blank row. I have other queries based on that one, and when it shows nothing, everything else does as well. I was wondering if you all knew of a way to show 0 when there are no hogs that meet this criteria. I have tried all of these:

    HeadCount: IIf(Sum([HeadNumber]) Is Null,0,Sum([HeadNumber]))
    HeadCount: IIf(Sum([HeadNumber])=Null,0,Sum([HeadNumber]))
    HeadCount: IIf(Sum([HeadNumber])<1,0,Sum([HeadNumber]))

    in the query field name but still couldn't show 0 as an output.

    Any thoughts on this subject? I would appreciate it!

  2. #2
    Join Date
    Sep 2003
    Posts
    7

    Re: Query Help Please

    Try this:
    HeadCount: IIf(IsNull(Sum([TableName].[HeadNumber])),0,Sum([TableName].[HeadNumber]))

    OM Gang


    Originally posted by decad1
    Hello all.

    I have a query where I am trying to find the number of hogs that meet certain characteristics. On occasion, however, the query comes up with none, and instead of having 0 as an output, it has nothing, not even a blank row. I have other queries based on that one, and when it shows nothing, everything else does as well. I was wondering if you all knew of a way to show 0 when there are no hogs that meet this criteria. I have tried all of these:

    HeadCount: IIf(Sum([HeadNumber]) Is Null,0,Sum([HeadNumber]))
    HeadCount: IIf(Sum([HeadNumber])=Null,0,Sum([HeadNumber]))
    HeadCount: IIf(Sum([HeadNumber])<1,0,Sum([HeadNumber]))

    in the query field name but still couldn't show 0 as an output.

    Any thoughts on this subject? I would appreciate it!

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Query Help Please

    Try this:
    HeadCount: Sum(IIf(IsNull([TableName].[HeadNumber]),0,[TableName].[HeadNumber]))
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57

    Re: Query Help Please

    Thanks for the replies. I plugged your expressions in, but it just won't quite put 0 for an output.
    Last edited by decad1; 09-11-03 at 09:34.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    just an idle thought...

    wont Sum([Whatever]) get upset if any instance of [Whatever] is null?

    if this is true, solution is either to disallow nulls in the table or to do a two-step: first query replaces nulls with 0 (if this is appropriate), second query does the sum([knownToBeNotNull])

    izy

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wont Sum([Whatever]) get upset if any instance of [Whatever] is null?
    nope

    aggregate function ignore nulls

    on purpose

    that's the way they're designed


    rudy
    http://r937.com/

  7. #7
    Join Date
    Sep 2003
    Location
    Sunshine Coast, Australia
    Posts
    6
    Have you tried the Nz function?
    Nz replaces any Nulls with the value after the comma.

    Sum(Nz([HeadNumber],0))
    or
    Nz(Sum([HeadNumber]),0)
    Last edited by Bean Man; 09-12-03 at 02:51.

  8. #8
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57
    I appreciate the replies. After looking this over, I doubt that under the circumstances that there will ever be a time when there will be a null value. Enough records will be entered at any given time that when the report is run, the query will have at least one record that meets this criteria.

    Thanks, I can use these on other things.

Posting Permissions

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