Results 1 to 8 of 8
  1. #1
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50

    Doing calculations using a Null field

    First, it should be known that I am not well-versed in SQL or VBL. I merely put together queries the amateur way.


    I am having an issue whereby I need to do calculations in which one of the fields can (in some cases) be Null.
    I have used the Nz function to put a Zero in the field on reports (or in the query) but Access doesn't recognize this as a true Zero.
    How do I go about being able to do a calculation using the field that is Null?
    Here is an example:

    Click image for larger version. 

Name:	Access screenshot.png 
Views:	27 
Size:	22.1 KB 
ID:	25415


    So here I know that [Ttl CCS >0 + PLA2>=200] should give me a 1. But because the field [Total Calcium Scores>0] has no records it returns a Null value.
    When I try to add a field that has a value to a field that does not have a value, I get no results therefore, having used the NZ function in the query it uses a Zero as a placeholder.

    Can anyone guide me on how to do this seemingly simple calculation.

    thanks to All.
    Attached Thumbnails Attached Thumbnails Access screenshot.png  

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Well, first off, you shouldn't have spaces in object names. A better name for field "Total Calcium Scores" would be "TotalCalciumScores" or even "Total_Calcium_Scores".
    Because of the spaces in the field names. you must enclose the field name in brackets. However, you have the brackets in the wrong places.

    "[Total Calcium Scores>0]" should be "[Total Calcium Scores] >0". Or "([Total Calcium Scores] >0)".

    The formula "[Ttl CCS >0 + PLA2>=200]" would/might be "([Ttl CCS] > 0) + (PLA2 >= 200)".
    If a field could be NULL, try "(NZ([Ttl CCS],0) > 0) + (NZ(PLA2,0) >= 200)".

    Note: (NZ([Ttl CCS],0) > 0) will result in a 0 or -1. Same for (NZ(PLA2,0) >= 200). So your possible results will be 0, -1, -2 (provided that the fields [Ttl CCS] & PLA2 are numeric, not text).

    Also Note: I'm not sure where to place the parenthesis.

  3. #3
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50
    Thanks for your help.
    First, the field names that you see in my query results are just labels . They are not showing the real calculations within the query design.
    These calculations work fine UNLESS one of my fields has any NULL field within the record. Then it is unable to do a calculation. In other words NULL doesn't really mean zero. If I had a zero then I could actually perform a calculation.
    The problem with the Nz function is that it is not really forcing a zero value only a zero as a placeholder so it appears to be zero (or whatever else you tell it to display).
    So, for example
    • If my PLA2>=200 filed in the query does not return any records that have fields that are positive for that criteria (>=200) AND
    • My CCS >0 has 12 records
    • My results end up zero when trying to add the two because PLA2>=200 comes back NULL

    I have spent days trying to develop queries that will resolve my issue. YUK!

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    In other words NULL doesn't really mean zero.
    You are right in that calculations with Null will often result in a Null result. So, we get around that with the Nz function. However, you are going to have to decide what a Null really DOES mean, if it doesn't mean zero - you have to replace it with something the calculation can use.

    If your data structure lets you do it, your other option might be to use an Iif function to have two versions of the calculation, one of which doesn't use the field which has the Null:

    Iif(isnull([field1]), expression without [field1], expression with [field1])

    This way you are not using the misleading 0 value at all.

    I forgot to mention that in this case the Nz has to be applied to each field individually, not to the result as a whole.
    Last edited by John_G; 08-23-2016 at 12:50 PM. Reason: Add more info

  5. #5
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50
    Thank for the reply John,
    This very short reply creates a very long thought process for me. I am understanding what you are saying but I need to digest this and think through it.
    It is true that zero does mean something and anything greater than zero means something.
    I will certainly reply once I decide what to try next and see what the result is.

    Thanks to everyone on this forum that takes the time to help folks like myself.

    Mark

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    First, the field names that you see in my query results are just labels . They are not showing the real calculations within the query design.
    So will you post the SQL of the query?



    My results end up zero when trying to add the two because PLA2>=200 comes back NULL
    But if you use the NZ() function, as far as the calculation is concerned, the NULL becomes a zero and the calculation completes. See attached dB.
    Attached Files Attached Files

  7. #7
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50
    I think, just maybe....this may have resolved SOME of my problems.
    the simple attached database and the trick of applying the Nz function individually in the calculated fields may have done the trick. I need to apply this several calculations to see that it resolves all of my issues.

    thanks to Steve and John.
    I appreciate you guys.

    I'll be back to let you guys know how this goes.

  8. #8
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50
    Hey guys,
    this has done the trick.
    Applying the Nz function to each field individually within the calculation was the key. It does create a Zero (when necessary) that is truly calculable.
    My biggest issue with fixing my calculations was the syntax. Adding the ROUND function was a real challenge with respect to having the parentheses and commas in the correct spots.
    I am permanently cross-eyed now.

    Thanks for all the advice.

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

Similar Threads

  1. Replies: 2
    Last Post: 02-18-2016, 07:12 AM
  2. Replies: 30
    Last Post: 09-30-2015, 10:58 AM
  3. Replies: 3
    Last Post: 11-13-2013, 08:56 AM
  4. If/Then Null based on Null Value of Another Field
    By vbpeterson in forum Queries
    Replies: 11
    Last Post: 06-05-2012, 05:00 PM
  5. Calculations with a multiple value field
    By ashwin09 in forum Forms
    Replies: 12
    Last Post: 07-31-2011, 11:22 AM

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