DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: IsNULL function in WHERE Clause in SQL statement???

  1. #1
    DH Guest

    IsNULL function in WHERE Clause in SQL statement???


    Hello ALL:

    Can I use IsNull as FilterCondition in WHERE clause in SQL statement to see
    if fields are NULL (I defined AllowZeroLength = True in TableDef for an ACCESS
    MDB).

    It looks like:

    "SELECT * FROM TABLE1 WHERE FLD1='" & Valu1 & "' AND ISNULL(FLD2);"

    OR any other alternative way to check Null value in WHERE clause.

    Thanks for your help.


  2. #2
    Jim Guest

    Re: IsNULL function in WHERE Clause in SQL statement???


    "DH" <nannanhu@aol.com> wrote:
    >
    >Hello ALL:
    >
    >Can I use IsNull as FilterCondition in WHERE clause in SQL statement to

    see
    >if fields are NULL (I defined AllowZeroLength = True in TableDef for an

    ACCESS
    >MDB).
    >
    >It looks like:
    >
    >"SELECT * FROM TABLE1 WHERE FLD1='" & Valu1 & "' AND ISNULL(FLD2);"
    >
    >OR any other alternative way to check Null value in WHERE clause.
    >
    >Thanks for your help.
    >

    '***************************************
    sSql = "Select * from info "
    sSql = sSql & "Where Name = '" & "24" & "' "
    sSql = sSql & "AND isnull(Address) "

    rs.Open sSql, Conn, adOpenKeyset, adLockPessimistic

    List1.AddItem rs.Fields("Name").Value

    I ran this and it worked for me. So as far as I can see your answer is Yes
    it will work.

    Hope this helps.

    Jim

  3. #3
    Douglas J. Steele Guest

    Re: IsNULL function in WHERE Clause in SQL statement???

    "SELECT * FROM TABLE1 WHERE FLD1='" & Valu1 & "' AND FLD2 IS NULL"

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele


    "DH" <nannanhu@aol.com> wrote in message news:3cda918b$1@10.1.10.29...
    >
    > Hello ALL:
    >
    > Can I use IsNull as FilterCondition in WHERE clause in SQL statement to

    see
    > if fields are NULL (I defined AllowZeroLength = True in TableDef for an

    ACCESS
    > MDB).
    >
    > It looks like:
    >
    > "SELECT * FROM TABLE1 WHERE FLD1='" & Valu1 & "' AND ISNULL(FLD2);"
    >
    > OR any other alternative way to check Null value in WHERE clause.
    >
    > Thanks for your help.
    >




  4. #4
    Michael Cole Guest

    Re: IsNULL function in WHERE Clause in SQL statement???

    DH <nannanhu@aol.com> wrote in message news:3cda918b$1@10.1.10.29...
    > Hello ALL:
    >
    > Can I use IsNull as FilterCondition in WHERE clause in SQL statement
    > to see if fields are NULL (I defined AllowZeroLength = True in
    > TableDef for an ACCESS MDB).


    Note that Null and a Zero-Length string are not the same thing. Check what
    you are looking for.

    >
    > It looks like:
    >
    > "SELECT * FROM TABLE1 WHERE FLD1='" & Valu1 & "' AND ISNULL(FLD2);"


    The VB Language and the SQL language are not the same thing. Get yourself a
    book on SQL. "IsNull" is a VB Function. In SQL check if "IS NULL".

    "SELECT * FROM TABLE1 WHERE FLD1='" & Valu1 & "' AND FLD2 IS NULL;"



  5. #5
    Joe \Nuke Me Xemu\ Foster Guest

    Re: IsNULL function in WHERE Clause in SQL statement???

    "DH" <nannanhu@aol.com> wrote in message <news:3cdbe051$1@10.1.10.29>...

    > I should say my Table1's default value should be Zorelength, mean EMPTY,
    > by TableDef.
    >
    > Than my question is that can I use EMPTY(FiledsName) to check it in my WHERE
    > clause?


    A zero-length text string is just '', so use:

    select mytable.foo, mytable.bar from mytable where mytable.foo = ''

    --
    Joe Foster <mailto:jlfoster%40znet.com> Wanna buy a Bridge? <http://xenu.net/>
    WARNING: I cannot be held responsible for the above They're coming to
    because my cats have apparently learned to type. take me away, ha ha!



  6. #6
    DH Guest

    Re: IsNULL function in WHERE Clause in SQL statement???


    Michael:

    Thanks your points!

    I should say my Table1's default value should be Zorelength, mean EMPTY,
    by TableDef.

    Than my question is that can I use EMPTY(FiledsName) to check it in my WHERE
    clause?

    Thanks.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center