Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    Unanswered: IS NULL query problem

    Hi Folks - a little problem thats baffling me......

    Im using SQL 2000 db

    Im querying a field using 'IS NULL' as the criteria, if I add data to the field and then delete and then try to query using 'IS NULL' as the criteria then the record will not be returned in my query even though the fields empty......

    Can anyone help please ?

    Thanks in advance

    DOnald

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    Is the field really empty (NULL)? Or is there an empty string now? They are different in SQL Server.

  3. #3
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    IS NULL Problem

    Hi - in the database it shows as an empty string (nothing in it!)

    So I need the criteria to query IS NULL and an empty field if that makes sense......

    Any idea's... ?

    Thanks for the quick reply !

    Donald

  4. #4
    Join Date
    Nov 2002
    Posts
    272
    SELECT *
    FROM yourTable
    WHERE yourField IS NULL OR yourField = ''

    should do the trick.

  5. #5
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    Sorted !

    Many thanks it worked !

  6. #6
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    ASP Query

    The worked fine in SQL, but not using VB / ASP....

    Here's the string of code that im having problems with;

    <%If BecamePartAss <> "" or not isnull(BecamePartAss) then%>

    The above works fine if the field 'IS NULL', but when an empty string exists then your code does not - as below;

    SELECT *
    FROM yourTable
    WHERE yourField IS NULL OR yourField = ''

    Thsi may be out of your realm here, but would like you to take a look if you can ?

    Regards

    Donald

  7. #7
    Join Date
    Nov 2002
    Posts
    272
    I'm sorry; I'm not that good at VBScript.
    Hopefully someone else can help you with that.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    how are you connecting exactly?

    Try WHERE ISNULL(yourField, "") = ""
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    When you want to "empty" a column, can't you just assign it a NULL value instead of using an empty string? That sure sounds like the best long term answer to me.

    -PatP

  10. #10
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    Assign Null Value

    Hi - that sounds the best course of action, would you have any information on how to do this ?

    Thanks

    Donald

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Please post the code you're using to assign an empty string, and I'll gleefully modify it to use a NULL instead.

    -PatP

  12. #12
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    The Code

    Hi Pat - below is the line of code that has the query in thats not working, I have also attached the page.

    The code is at line 225 on teaminfo.asp

    <%If BecamePartAss <> " " or not isnull(BecamePartAss) then%>

    Its an SQL 2000 db, and the field is varchar 50

    This problem has come about I have entered text in the field and then deleted, then when I query looking for nulls it does not see an 'empty string'...

    Many thanks for taking time to help.

    Donald
    Attached Files Attached Files

  13. #13
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by donaldt
    <%If BecamePartAss <> " " or not isnull(BecamePartAss) then%>
    This problem has come about I have entered text in the field and then deleted, then when I query looking for nulls it does not see an 'empty string'...
    Your code doesn't check for empty string. It checks for a string containing a space.

  14. #14
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    Hi Ivon

    Quote Originally Posted by ivon
    Your code doesn't check for empty string. It checks for a string containing a space.
    I tried it like tis too;

    <%If BecamePartAss <> "" or not isnull(BecamePartAss) then%>

    Can you tell me where im going wrong.... ?

    Donald

  15. #15
    Join Date
    Nov 2002
    Posts
    272
    No, I can't, I'm sorry.
    I see you use the same code for two other fields. Do they work with empty strings?

Posting Permissions

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