The problem is that Access does not trigger the request:

FORMULA:

(SELECT Sum(tblPok.PokSumCom) AS [Sum-PokSumCom] FROM tblPok WHERE [Client] = <Client>) + (SELECT Sum(tblCentrPok.PokSumCom) AS [Sum-PokSumCom] FROM tblCentrPok WHERE [Client] = <Client>)

If you just take SELECT Sum(tblPok.PokSumCom) AS [Sum-PokSumCom] FROM tblPok WHERE [Client] = <Client> then everything works for one table and the corresponding row. Accordingly, if you add both sums (as in the initial formula) and one of the NULL results, nothing works at all.

The essence of the question: how to make the answer set to NULL automatic value of 0?
I tried various options: ISNULL(SUM(),0) , COALESCE(SUM(),0) , etc. All in vain.

Are there any other options?

  • So you are trying to add two data sets? - Grundy

1 answer 1

Put a frame select isnull (Sum (tblPok.PokSumCom), 0)

if there is no such thing

 select isnull( (select sum() .... ) , 0)

here is the complete answer

  select isnull(SELECT Sum(tblPok.PokSumCom) FROM tblPok WHERE [Client] = <Client>), 0) AS [Sum-PokSumCom]
  • select isnull (SELECT Sum (tblPok.PokSumCom) AS [Sum-PokSumCom] FROM tblPok WHERE [Client] = <Client>), 0) - so? - user204443
  • Almost, it is also possible, alias at the end, if it is needed (the field will be noname) select isnull (SELECT Sum (tblPok.PokSumCom) FROM tblPok WHERE [Client] = <Client>), 0) AS [Sum-PokSumCom] - nick_n_a
  • does not work ... logically everything is OK, but no, and even if you are cracked - user204443
  • Maybe your cursor is incorrectly configured? And what does select 0 give? And what does select isnull (null, 0) give? And what does select isnull (1,0) give? 0,0,1 answer? (Verification can the request not written there) - nick_n_a
  • I am testing. The problem is that it all works through the program, and not in the access itself (the "Customer Account" program) - user204443