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
Hello Bob, I've managed to change it to a general number, however, the calculation still isn't working. Is my BODMAS correct?
Where is BalanceAPS ??
Can you show us the revised calculation with your numbers?I've managed to change it to a general number
See below:-
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
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)"
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.
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])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])
This
Will add BalanceAPS to INR then deduct the deductions.Code:SubTotal: (Nz([BalanceAPS],0)+Nz([INR],0))-Nz([Deductions],0)
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 ↓↓
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)
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
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.