Moving from Access to SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am haveing a problem with this.

Sum(nz([UnitsReceived])-nz([UnitsSold]))

i can not figure out what i need to change it to to work with SQL.

Thanks in advance
 
Jon said:
I am haveing a problem with this.

Sum(nz([UnitsReceived])-nz([UnitsSold]))

i can not figure out what i need to change it to to work with SQL.

Thanks in advance

Sum(IsNull([UnitsReceived], 0) - IsNull([UnitsSold], 0))
 
Hi,



You mean MS SQL Server? Replace NZ by COALESCE, but explicitly supply the
value 0:


SUM( COALESCE( UnitsReceived, 0) - COALESCE( unitsSold, 0) )


Note that with COALESCE, you can have more than two arguments: the first
not null argument is returned, and if all arguments are NULL, NULL is
returned. You can also use ISNULL( arg1, arg2), but ISNULL is limited to 2
arguments, may introduce confusion with IS NULL in two words, and seems
less compliant with the SQL Standard. For these reasons, I suggest to use
COALESCE.


Hoping it may help,
Vanderghast, Access MVP
 
thanks it worked great

Rick Brandt said:
Jon said:
I am haveing a problem with this.

Sum(nz([UnitsReceived])-nz([UnitsSold]))

i can not figure out what i need to change it to to work with SQL.

Thanks in advance

Sum(IsNull([UnitsReceived], 0) - IsNull([UnitsSold], 0))
 
Back
Top