Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Location
    New Salem, North Dakota, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    quick help (access 2000)

    How do you sum fields with a null value. When I sum a group that has some null values the answer is null??

    Thanks,
    Kristen

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

    Re: quick help (access 2000)

    How are you calculating the sum? The Sum function ignores all null values and adds the non-null values, so if you put =Sum([NameOfField]) in the group footer (I assume this is about a report), the result should be non-null unless ALL values are null.

  3. #3
    Star Lounger
    Join Date
    Oct 2002
    Location
    New Salem, North Dakota, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: quick help (access 2000)

    It is actually in a query so it is mutiple fields. We are using the Sum function.

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

    Re: quick help (access 2000)

    Could you post the SQL of the query?

  5. #5
    Star Lounger
    Join Date
    Oct 2002
    Location
    New Salem, North Dakota, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: quick help (access 2000)

    Sum([1 - Probability]+[2 - Impact People]+[3 - Impact Property]+[4 - Impact Sales]+[5 - Mitigation Internal]+[6 - Mitigation External]) AS [threat totals], [Threat Types].Facility
    FROM [Threat Types]

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

    Re: quick help (access 2000)

    You don't need Sum here, the + operators will do the adding. To make null values count as 0, use the Nz function for each of the contributing fields:

    Nz([1 - Probability],0)+NZ([2 - Impact People],0)+Nz([3 - Impact Property],0)+Nz([4 - Impact Sales],0)+Nz([5 - Mitigation Internal],0)+Nz([6 - Mitigation External],0) AS [threat totals]

Posting Permissions

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