How to deal with null value in SELECT statement RRS feed

  • Question

  • Hi

    I'm using Visual Basic 2005.

    In the query builder I write the following statement:

    SELECT       Center_Cost, Services_Cost, Salry, Income,year,Center_Cost+ Center_Cost + Salry AS Total_Cost
                             FROM            (((Query41 LEFT OUTER JOIN
                             Query52 ON Query41.year = Query52.year) LEFT OUTER JOIN
                             Query48 ON Query41.year = Query48.year) LEFT OUTER JOIN
                             Query42 ON Query41.year= Query42.year)


    I want to get the total of 3 fields, but when one of these fields equal to null, it makes the Total_Cost equal to null

    I tried to put Is Null Before the field name, but it gives me ( -1 ) when it's equal to null

    I also tried to put Is Numeric it  gives me the same result

    as an example of the problem:

    serives cost= 1200

    center cost= null

    salray= null

    total cost=  null

    I want the result to be displayed like the follwing:

    serives cost= 1200

    center cost =0

    salray =0

    total cost =1200

    please if anyone can help me

    thanks alot


    Sunday, December 23, 2007 9:54 PM


  • You want to use the ISNULL function, not the IS NULL operator.  So, ISNULL(Center_Cost, 0), which returns 0 if Center_Cost is null.

    Monday, December 24, 2007 4:47 AM