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

Solution 1

One or more of the column values is NULL, and this can not be converted to an integer so is causing this error. Try wrapping the value in the Nz function e.g. Nz([My_value],0)

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

Solution 2

One of your Columns has the value NULL. Then the result from your calculation would be NULL and you tried to convert to an Integer which would yield the error you are seeing.

Try this adjustment to your SQL Query:

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