Results 1 to 5 of 5
  1. #1
    JimR is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2012
    Posts
    3

    Table calculated field that includes null value

    I would like to create a calculated field in a table, which sums the values of several other fields. If I use the formula Total = [FieldA]+[FieldB]+[FieldC]+[FieldD], I will get a value only if there is an entry for each field. What I want to get is a total even if one of the fields has no value entered. I've considered setting a field default value to "0" for the fields in the equation, but that could be misleading because some of the fields may actually have a value of Zero although others are intentionally left blank. Previously I did the calculation in queries and used "nz" prefix to allow the formula to work with null values. That doesn't function in a table calculation.



    How can I sum values to create a calculated field in a table when one or more of the cells used in the calculation is actually a null value (no entry) rather than Zero?

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My recommendation would be to avoid calculated fields in tables (that also applies to Look up FIELDS in tables and Multi-value fields).
    In my view, calculated fields have serious shortcomings.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    How can I sum values to create a calculated field in a table when one or more of the cells used in the calculation is actually a null value (no entry) rather than Zero?
    in a table calculation, I don't believe you can. One of it's limitations.

  4. #4
    JimR is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2012
    Posts
    3
    Thanks for the advice - I guess I'll go back to the old style and do all of the calculations in queries.

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Thanks for the advice - I guess I'll go back to the old style and do all of the calculations in queries.
    "Older" is actually considered "better" in this case. It is the generally the "best practice" method for a myriad of reasons, including that no other database program (including the much more robust SQL and Oracle) allow for calculated fields in Tables. So if you ever wanted to convert your Access database to one of these programs, you would have issues you would need to account for.

    It really isn't a big deal to do it in a Query; you don't really lose anything, as you can use a Query for pretty much anything you can use a Table for.
    And, it is generally considered good practice to keep users out of the Tables completely. Manual data entry should be done via Forms, and not Tables directly, where you can control it. And Forms can use Queries as their Data Source.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculated field in web table with null values
    By Bryan Daniels in forum Access
    Replies: 13
    Last Post: 07-08-2016, 03:22 PM
  2. Replies: 1
    Last Post: 09-20-2014, 05:12 PM
  3. Replies: 1
    Last Post: 04-21-2013, 03:20 PM
  4. Replies: 3
    Last Post: 02-13-2013, 10:15 AM
  5. Replies: 8
    Last Post: 11-04-2011, 06:52 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums