Results 1 to 14 of 14
  1. #1
    Blings's Avatar
    Blings is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    London, UK
    Posts
    125

    Calculation


    SubTotal: (Nz([BalanceAPS],0)+Nz([INR],0))-Nz([Deductions],0)

    Is my SubTotal code above correct? It's not adding INR.

    Click image for larger version. 

Name:	INR_calculation.PNG 
Views:	31 
Size:	38.1 KB 
ID:	46096

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    R8 441,00 is NOT a number
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Blings's Avatar
    Blings is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    London, UK
    Posts
    125
    Hello Bob, I've managed to change it to a general number, however, the calculation still isn't working. Is my BODMAS correct?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Where is BalanceAPS ??

    I've managed to change it to a general number
    Can you show us the revised calculation with your numbers?

  5. #5
    Blings's Avatar
    Blings is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    London, UK
    Posts
    125
    See below:-
    Attached Thumbnails Attached Thumbnails qryForINR_calc.jpg  

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    So how are you meant to confirm your math, if you do not include the values in the query?

    Plus payment appears to be dependent on subtotal?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Blings's Avatar
    Blings is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    London, UK
    Posts
    125
    The SubTotal is pulling the BalanceAPS.

    I think all I am trying to understand is if my Syntax in this query is correct "SubTotal: (Nz([BalanceAPS],0)+Nz([INR],0))-Nz([Deductions],0)"

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IMO you will never be able to add the numeric part of a string if any alpha characters precede any numeric ones. You will have to strip them out first. Why oh why would a field meant to be summed have alpha characters in it?

    EDIT - or are you saying you fixed that? Then the expression looks like it should not error, unless perhaps any of the fields have a zls rather than null.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    What happens if you use IIF() instead of NZ()?

    Code:
    SubTotal: ( iif([BalanceAPS] is null,0, [BalanceAPS]) + iif([INR] is null,0, [INR]) ) - iif([Deductions] is null,0, [Deductions])

  10. #10
    Blings's Avatar
    Blings is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    London, UK
    Posts
    125
    Quote Originally Posted by kd2017 View Post
    What happens if you use IIF() instead of NZ()?

    Code:
    SubTotal: ( iif([BalanceAPS] is null,0, [BalanceAPS]) + iif([INR] is null,0, [INR]) ) - iif([Deductions] is null,0, [Deductions])
    You may have entered an operand without an operator, highlights the [BalanceAPS])

  11. #11
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    This
    Code:
    SubTotal: (Nz([BalanceAPS],0)+Nz([INR],0))-Nz([Deductions],0)
    Will add BalanceAPS to INR then deduct the deductions.

    This appears to be exactly what your first picture is displaying, if that isn't what you wanted then your formula is wrong.
    I can't tell if the rest is correct or not because you query picture doesn't shows us the full SQL of each calculation.

    The NZ() function is definitely the best way to handle this rather than nesting IIf() statements.

    I would suggest you post up the complete SQL of your query - but please format so we can read it.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  12. #12
    Blings's Avatar
    Blings is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    London, UK
    Posts
    125
    My only issue is the formula for adding [BalanceAPS]+[INR] together. Everything else works. I tried your above code which also did not want to add them together. Expert advice on the formula is all I need

    Code:
    SubTotal: (Nz([BalanceAPS],0)+Nz([INR],0))-Nz([Deductions],0)

  13. #13
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Blings View Post
    Hello Bob, I've managed to change it to a general number, however, the calculation still isn't working. Is my BODMAS correct?
    "isn't working" doesn't tell us much about what's wrong. Perhaps another screen shot like the one in post #1 might help.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  14. #14
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Minty View Post
    This
    Code:
    SubTotal: (Nz([BalanceAPS],0)+Nz([INR],0))-Nz([Deductions],0)
    The NZ() function is definitely the best way to handle this rather than nesting IIf() statements.
    I didn't suggest *nested* iif statements, but simply drop in replacements for nz. Just something to try to avoid vba calls (nz) and variant data types / strings per the linked article.

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

Similar Threads

  1. APR Calculation
    By itnchans in forum Modules
    Replies: 1
    Last Post: 04-11-2016, 06:11 AM
  2. Sum If Calculation Help
    By CT_AccessHelp in forum Access
    Replies: 8
    Last Post: 03-08-2016, 10:13 AM
  3. Help with a calculation
    By Cdray in forum Programming
    Replies: 9
    Last Post: 06-26-2015, 09:37 AM
  4. Calculation Field in table vs Query calculation
    By Leonidsg in forum Database Design
    Replies: 18
    Last Post: 02-28-2013, 09:38 PM
  5. Calculation value not less than 1
    By vbpeterson in forum Queries
    Replies: 10
    Last Post: 08-17-2011, 06:41 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