Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding the values of 2 fields (2002)

    I'm sure the answer is easy, but I can't find it. I created a query that calculates separately the sum of two decimal fields. In the report, I am adding these two fields to come up with the grand total. It works as long as there are values in both fields, but if one is empty, it doesn't do the sum. My math tells me that 1+0=1, so I don't understand why it doesn't work. Is there a way of doing this without creating an "If" statement?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding the values of 2 fields (2002)

    Use the Nz Fuction.
    Total : Nz([Field1],0) + Nz([Field2],0)
    Nz will replace the Null by zero in the calculation.
    Francois

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

    Re: Adding the values of 2 fields (2002)

    Francois already provided the solution.

    As an explanation: an empty field ("Null" in database terms) is not the same as a zero value. It means that the value is missing, lacking. Any calculation direclty involving Null values will return Null, indicating that there's not enough information. If you want to force Access to treat Null values as zero, you need to use the Nz function, as suggested by Francois.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding the values of 2 fields (2002)

    Thanks. Worked like a charm. What fooled me was the default values of the fields in the table. I had them set as zero, which to me means that the field is not null, but the numerical value zero. Obviously, this is not the case.

    By the way, is there a casebook on Access written? I have a couple that are as thick as Bibles, but they are boring, too technical (above my knowledge level) and not project driven. I know enough about Access to be dangerous, but still struggle with many aspects. My strength is to take complex ideas and explain them simply. For example, I am still struggling with the cascading combo boxes. If there was a book that talked about how Hans was building a database to do x, y and z, and here is what he did step by step to achieve it. I know every situation is different, but there must be something we can do.

    What are some examples of projects that come to mind?
    A school with teachers, courses, students, marks, timetables (which is sort of what I am building now).
    A football league with teams, players, schedules, games, scores, statistics, standings.

    Got to run now. I am going to teach soon.

Posting Permissions

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