Equivalent Functionality to SQL COALESCE function

[SimonPGreen]

Hi,

Tryin to crack something using JET SQL that works in T-SQL using the COALESCE function to calculate a movement as follows:

Item Value1 Movement
1 100 100
2 150 50
3 220 70

In T-SQL the following select returns the above:

SELECT Test.item, Test.Value1,Test.Value1 - COALESCE(test1.value1,0) as Movement
FROM Test Test
LEFT JOIN Test Test1 ON (Test.item-1)= test1.item

In JET SQL after changing the code I have tried using the following to generate the Movement in place of COALESCE:

Test.Value1 - Nz([Test1].[Value1],0) AS Movement &
Test.Value1 - iif(IsNull([test1].[value1]),0,[test1].[value1])) AS Movement

Both of the above JET Queries return the following missing the first row:

Item Value1 Movement
2 150 50
3 220 70

Jet Code:

SELECT test.item,test.value1,test.value1-Nz(test1.value1,0) as movement FROM test INNER JOIN test AS Test1 ON (test.item-1)=test1.item

Really appreciate any help.

Regards,

Simon

 

[AlexCuse]

What about this?

iif(IsNull([test1].[value1]) = True,0, [test1].[value1])) AS Movement

or this?

iif([test1].[value1] is null, 0, [test1].[value1])

IsNull in access returns a boolean value.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Golom]

You has a LEFT JOIN in your T-SQL code but you have changed it to an INNER JOIN in Access. It does need to be a LEFT JOIN.

 

[SimonPGreen]

Guys,

I owe you - thanks

Simon