CODEWITHSUNDEEP
sqlms-accessdebugging
Tony The Lion
Tony The Lion

Reputation: 63190

SQL MS Access - Invalid Use of Null

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

Upvotes: 3

Views: 18607

Answers (3)

Summer-Time
Summer-Time

Reputation: 1874

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

Upvotes: 3

Kevin Ross
Kevin Ross

Reputation: 7215

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.

Upvotes: 4

Mitch Wheat
Mitch Wheat

Reputation: 300509

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

Upvotes: 0

Related Questions