SQL MS Access - Invalid Use of Null

Question

I have now got rid of the overflow on this query by forcing to a long but now I get

Error 94: Invalid Use of NULL

Can anyone tell me what the problem could be?

SQL Query:

Sum(CLng(
  [TotaalPrijs]/([tbl_ArtikelsPerOrder]![Aantal]*[Totaal])*
  [tbl_ArtikelVerwijderdUitZaaglijst]![Aantal]
)) AS GezaagdeOmzet

Answer 1

Impossible to say for sure without more information, but is TotaalPrijs or Aantal NULL in your data?

Answer 2

In an Column stands the Value NULL. Then the result from your calculation would be NULL an then you tried to Convert to an Integer.

SQL Query:

Sum(CLng(nz([TotaalPrijs]/([tbl_ArtikelsPerOrder]![Aantal][Totaal])[tbl_ArtikelVerwijderdUitZaaglijst]![Aantal],0)) AS GezaagdeOmzet

Answer 3

One or more of the values returned it NULL, this can not be converted to an integer so it is causing this error. Try wrapping the value in a NZ function i.e. NZ([My_value],0)

This will force it to return 0 if a NULL is found