How to convert null values into 0

[#1]

Hi experts,

My source is an excel file.

I want to convert null values ( - ) into 0

I've tryed this but it doesn't work :

IF(ISNULL(ItemName)=0, ItemName, '0' ) AS ItemName

[nagaiank]

Is this a duplicate post? For the other post, I had replied as follows:

Try this instead.

If (IsNull(ItemName), 0, ItemName)

[#3]

0 is false, -1 is true so try

if(isNull(ItemName)=-1,ItemName,0) as 'ItemName'

I don't actually know if this will do what you want but that could be the problem.

Also try using if(len(ItemName)=0,0,ItemName) as 'ItemName'

I've heard using len is better than isNull but off the top of my head I'm not sure why.  Might be b/c len catches missing as well as null?  Or maybe it's faster?  I don't know.

[Anonymous]

If my memory serves me correctly there used to be a difference between what values were caught by Isnull() depending on if the QV instance was 32 or 64-bit. However, I'm pretty sure that was subsequently changed so that they function the same way. Anyway, I moved over to Len(Fieldname)=0 and haven't looked back.