[hai_anilreddy]

Hello.

i have a Problem when i was using Data Report in Visual Basic 6.0. I added a command and givien a query. Which takes data from two tables

example

table 1
ID Salary
1 100
2 200
3 300

table2
ID Comm
1 NULL
2 300
3 NULL

i have given a query like this to the Access Database

Select ID, (Salary + Comm) as Total_Salary from Table1,Table2;

The output is like this

ID Total_Salary
1 NULL
2 500
3 NULL

i have also tried using Nz(),ISNULL() and NVL() functions as given in Access,SQL Server and Oracle Respectively, but it has given error that Undefined Function in Expression

I want this Result

ID Total_Salary
1 100
2 500
3 300

can anybody help me out ..... Please

Have a Nice Day

[hyjacked]

I'm not sure of hte syntax exactly, but you would need an if statement to change your null to 0. Or that is how I would do it. it might be as straight forward as if then end if, but I know oracle uses a decode function to work it's ifs. I'll look around to see if I can find out for sure.

Basically though, you would change the comm entry in your query to

Code:

if isnull(comm) then 
  0 
else 
  comm 
end if

[hai_anilreddy]

HI hyjacked..

but i am using a join query and i need only a function which converts the data from NULL to ZERO like NVL() in oracle.

The query which i am actually using is like this

SELECT AB.Producer_ID, SUM(MC.B_Quantity) AS TBQ, (SUM(MC.B_Quantity) + SUM(MC.C_Quantity)) AS TTQ, SUM(MC.B_Amount) AS TBA, SUM(MC.C_Quantity) AS TCQ, SUM(MC.C_Amount) AS TCA, (SUM(MC.B_Amount) + SUM(MC.C_Amount)) AS TTA, MAX(MyDistribution.TDue) AS TD, (TTA - TD) AS TA FROM (AB INNER JOIN MC ON AB.Producer_ID = MC.Producer_ID) LEFT JOIN `Select Sum(Due) As TDue,Producer_ID from Distribution Group by Producer_ID`. AS MyDistribution ON MC.Producer_ID = MyDistribution.Producer_ID GROUP BY AB.Producer_ID;

And i need the function for this field (MyDistribution.TDue) AS TD

can u help me.

[hyjacked]

untested sql, first time I've seen this function, but if's are allowed in access I read

source: http://www.experts-exchange.com/Data..._20849971.html

MAX(Case MyDistribution.TDue
When NULL Then 0
Default MyDistribution.TDue
END) as TD