dcsimg
Results 1 to 11 of 11

Thread: [RESOLVED] sum of columns in sql which includes null values

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    Resolved [RESOLVED] sum of columns in sql which includes null values

    I'm trying to retrieve a field based on a condition as follows.

    A11, A13, A21 and A23 are the columns (Number data_type) may contain null values.

    My condtion is : Retrieve SubID where (A11+A13-A21) <> A.23

    I Put it in Sql like this which throws an error. How to handle Null Values in this case ? My DataBase is MS Access 2007

    vSql = "Select SubID from Table31 where Stat = 2 and (ISNULL(A11,0)+ ISNULL(A13,0)- ISNULL(A21,0)) <> (ISNULL(A23,0))"

  2. #2
    Addicted Member ryanframes's Avatar
    Join Date
    Apr 2012
    Posts
    209

    Re: sum of columns in sql which includes null values

    remove the under bracket after And
    Sorry for bad english.

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,499

    Re: sum of columns in sql which includes null values

    @ryan -- what?

    @raghavendran -- well since you didn't see fit to tell us what the error is, we'd only be guessing, which is what Ryan is probably doing. ... ah... OK.... IsNull is the problem... it doesn't work the same in Access as it does else where... in access it is a function which returns a boolean indicating if the value is null or not... the function you're trying to use there is NZ() ...

    ISNULL documentation - http://office.microsoft.com/en-us/ac...001228867.aspx
    NZ documentation - http://office.microsoft.com/en-us/ac...001228890.aspx

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    Re: sum of columns in sql which includes null values

    My Query is :

    vSql = "Select SubID from Table31 where Stat = 2 and (ISNULL(A11,0)+ ISNULL(A12,0)- ISNULL(A13,0)+ ISNULL(A14,0)- ISNULL(A15,0)+ ISNULL(A16,0)- ISNULL(A17,0)) <> ISNULL(A18,0)"
    Set vRs = New Recordset
    vRs.Open vSql, cnnRice, adOpenKeyset, adLockOptimistic

    These are the error messages I get with IsNULL and NZ

    Using NZ:
    Name:  Img1.jpg
Views: 196
Size:  13.5 KB

    Using ISNull:
    Name:  Img2.jpg
Views: 201
Size:  19.7 KB

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,499

    Re: sum of columns in sql which includes null values

    using IsNull is NOT going to solve your problem... stop trying to use it... it takes ONE parameter AND ONLY ONE... and will return true or false... not a number... thus the second error.
    as for why Nz isn't working, couldn't say since you didn't post that code...


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

  7. #7
    Addicted Member ryanframes's Avatar
    Join Date
    Apr 2012
    Posts
    209

    Re: sum of columns in sql which includes null values

    Quote Originally Posted by RhinoBull View Post
    If you are using MS SQL then IsNull is the way to go.
    If you are using MS Access then I think you need to use IIf(IsNull(...),...) sort of logic.
    ahh you ryt rhino, i didn't see that he's using MS Access before..
    Sorry for bad english.

  8. #8
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,423

    Re: sum of columns in sql which includes null values

    Nz(,) is the Access version of ISNULL(,) but it is a function of the Access application and not the Access driver, it will fail if you try to use it from outside the Access application, that's why you'll getting the "Undefined function 'NZ'.." error.

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    Re: sum of columns in sql which includes null values

    techgnome.. Here is my code using NZ.

    Code:
    vSql = "Select SubID from Table31 where Stat = 2 and (NZ(A11,0)+ NZ(A12,0)- NZ(A13,0)+ NZ(A14,0)- NZ(A15,0)+ NZ(A16,0)- NZ(A17,0)) <> NZ(A18,0)"
    Set vRs = New Recordset
    vRs.Open vSql, cnnRice, adOpenKeyset, adLockOptimistic

  10. #10
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,132

    Re: sum of columns in sql which includes null values

    As Rhino pointed out earlier, maybe you should try the IIF with IsNull logic
    Code:
    Select SubID from Table31 where Stat = 2 and ((IIF(IsNull(A11),0,A11) + IIF(IsNull(A12),0,A12) - IIF(IsNull(A13),0,A13) + IIF(IsNull(A14),0,A14) - IIF(IsNull(A15),0,A15) + IIF(IsNull(A16),0,A16) - IIF(IsNull(A17),0,A17)) <> IIF(IsNull(A18),0,A18))"

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    201

    Re: [RESOLVED] sum of columns in sql which includes null values

    Yes.. Yes... Yes... IIF(ISNull method resoves its... Thanks for all your support ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width