I am having abit of a situation and hope you can point me at the right direction.
1st DSUM (Text2):
=DSum("[quantity_ya7]","Stock","[part_number]= '" & [part_number] & "'")
2nd DSUM (Text2): (This can be Null at times as there are no withdrawals or records)
=DSum("[amt_ya7]","Withdrawal","[part_number]= '" & [part_number] & "' ")
After setting the 2 DSUM above, I will carry the subtract out.
=Text1-Text2
If there are values for to calculate for 2nd DSUM, the result will display in order. Else, it will be empty. (No values displayed)
How do I calculate as -0 (deduct 0) so I can get the right value displayed?
Thank you, much appreciated.
You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.
https://support.office.com/en-gb/article/Nz-Function-8ef85549-cc9c-438b-860a-7fd9f4c69b6c
In your case you want the DSUM to return 0 rather than Null when there's no value so you can use it in a calculation.
=NZ(DSum("[amt_ya7]","Withdrawal","[part_number]= '" & [part_number] & "' "),0)