checking NULL in SQL and replacing it with zero in VB6

[ayyazm64]

Hi,
I need to check NULL in SQL and replace it with zero.My database is in MS Access. Access has a function NZ which does this job when you are in Access.
but when used in SQL in VB6 and run it gives error, it doesn't recognize function.There is another function ISNULL but it just checks the value is NUll
and returns -1 if is null in SQL .
like select ISNULL(sum(quantity)) from inventary;
it returns -1 if it is null otherwise 0 means the result of this query is not the sum of quantity if it is not null but 0.

in select NZ(sum(quantity),0) from inventary;
it returns 0 if null otherwise the sum.
Please response quick!
Ayyaz

[VBFnewcomer]

I presume you just want to update the values in your tables to zero when the field is NULL.
why can't you just use a update query from vb
something like

Private Sub Form_Load()
  'Note you must prepare the connection obj beforehand
  Dim str As String
  str = "UPDATE " & yourTableName & " SET yourField = " & 0 & " WHERE " & IsNull(yourfield)
  conn.Execute (str)
End Sub

[anhn]

Equivalent of Nz(field1, 0) function:
IIf(field1 Is Null, 0, field1)

[VBFnewcomer]

anhn would it not be

IIf(Is Null(field1), 0, field1)

[anhn]

anhn would it not be

IIf(Is Null(field1), 0, field1)

No. It can be:
IIf(IsNull(field1), 0, field1)
but that uses a VB function IsNull(), this function is much slower than SQL checking field1 Is Null

[techgnome]

It's also possible that it will fail anyways.... one of the problems with IIF is that it evaluates ALL THREE parameters (it's a funciton) .... and will try to return the correct type (integer in this case) ... problem is that if field 1 is null, it's going to try to convert it to an integer (based on parameter 2)... but NULL can't be converted to integer.... which will cause an Invalid Use of NULL error.

ayyazm64 - I'm curious, what problems with NZ did you encounter? what does your SQL look like and how were you calling it? It should work.

-tg

[ayyazm64]

Hi,
How i can check if a table is empty(no records in it) in a SQL.
like
Select (select Q1 from tbl1)*(select Q2 from tbl2) + (select Q3 from tbl3) as M1 , Q4 from tbl4;

Now if tbl1 has no records in it ,M1 will be empty rather it should give me Q3.Actually the result of
(select Q1 from tbl1) is empty not NULL rather empty.
what i want is : if tbl1 is empty it should replace Q1 with zero.
PLease respond quick!
Ayyaz

[VBFnewcomer]

Dear anhn I actually meant IsNull() it was typo when I copied ur snippet.
and I was not aware of

field1 Is Null

. Thanks for making me aware
techgnome is also correct, I've read it somewhere earlier

[VBFnewcomer]

ayyazm64, what are you trying to do here

Select (select Q1 from tbl1)*(select Q2 from tbl2) + (select Q3 from tbl3) as M1 , Q4 from tbl4;

pls elaborate

[anhn]

It's also possible that it will fail anyways.... one of the problems with IIF is that it evaluates ALL THREE parameters (it's a funciton) .... and will try to return the correct type (integer in this case) ... problem is that if field 1 is null, it's going to try to convert it to an integer (based on parameter 2)... but NULL can't be converted to integer.... which will cause an Invalid Use of NULL error.

ayyazm64 - I'm curious, what problems with NZ did you encounter? what does your SQL look like and how were you calling it? It should work.

-tg

1. I don't have that problem with IIf() in Access.

IIf(Q1 Is Null, 0, Q1) * IIf(Q2 Is Null, 0, Q2) + IIf(Q3 Is Null, 0, Q3)

2. Nz() is an Access native function, it is not available outside of Access.

[ayyazm64]

Hi Anhn,
you have solved the Problem of NULL but now the question is:
How to check if a table is empty(no records in it) in a SQL.
like
Select (select Q1 from tbl1)*(select Q2 from tbl2) + (select Q3 from tbl3) as M1 , Q4 from tbl4;

Now if tbl1 has no records in it ,M1 will be empty rather it should give me Q3.Actually the result of
(select Q1 from tbl1) is empty not NULL,means if we check its result it is not null.what i want is : if tbl1 is empty it should replace Q1 with zero.
PLease respond quick!
Ayyaz

[anhn]

You can only have that if you use LEFT JOIN or RIGHT JOIN, otherwise the query will return zero record.

PLease respond quick!

That is a bad request!

[techgnome]

1. I don't have that problem with IIf() in Access.

IIf(Q1 Is Null, 0, Q1) * IIf(Q2 Is Null, 0, Q2) + IIf(Q3 Is Null, 0, Q3)

2. Nz() is an Access native function, it is not available outside of Access.

I don't do Access at all, so I wasn't sure. Thought I'd at least ask. I do know that that is a flaw of IIF in VB6.... it's possible that they "got it right" in the Access implementation.

Cool!
-tg

[ayyazm64]

Hi Anhn,
Let me put the question this way:

SELECT IIf([exp_amt] Is Null,0,[exp_amt])+IIf([opstock] Is Null,0,[openingstock])
FROM expenses, stock;

Now if any of the fields exp_amt or opstock is NULL it replaces it with zero
and returns the correct result But if one of the tables expenses or stock
is empty it returns blank not even Null.
Is there any way when one table is empty, it should take that field from that table as zero .
Ayyaz

[VBFnewcomer]

ayyazm64 take the advise given by techgnome in not using IIF, more particularly when you are dealing with database. I've sometime back (when I loved the shortness of code of IIF) used was using it, untill,... encountered unexpected results fortunately it was early stage of development so I was able to survive it. phew!
Remember IIF is not simple checking routine but a function
there are more ways to speak with db, eg, constructing the SQL stmt by concatenating a series of string.

[dee-u]

Hi Anhn,
Let me put the question this way:

SELECT IIf([exp_amt] Is Null,0,[exp_amt])+IIf([opstock] Is Null,0,[openingstock])
FROM expenses, stock;

Now if any of the fields exp_amt or opstock is NULL it replaces it with zero
and returns the correct result But if one of the tables expenses or stock
is empty it returns blank not even Null.
Is there any way when one table is empty, it should take that field from that table as zero .
Ayyaz

You mean to say there is no record in one table? If there is no record then there is nothing to check for nullity.

And why are the fields here different? They should be the same.

IIf([opstock] Is Null,0,[openingstock])