Thread: Table and Null

    #1
  1. No Profile Picture
    Contributing User
    ASP Good Citizen (1000 - 1499 posts)

    Join Date
    Feb 2005
    Posts
    1,280
    Rep Power
    36

    Table and Null


    I have a table that has a bunch of fields that are blank..
    I do a Is Not Null query and I get those records in the results....
    I tried to Trim the field but doesn't change anything...how can the field have value with nothing there?

    So I cant grab them by a Null query nor a Is Not Null query.
  2. #2
  3. No Profile Picture
    Contributing User
    ASP Good Citizen (1000 - 1499 posts)

    Join Date
    Feb 2005
    Posts
    1,280
    Rep Power
    36
    Still interested in Finding a solution as to why fields that are blank to the eye are coming up in a Is Not Null query

    In the mean time I simply added an "a" to the front of every record....
    Then did an update on all the records = "a" to "anone"
    Then went back and trimmed every record by 1 from the front, thus removing all the "a" I put in there. All records return to normal...all records with "anone" become "none"
    No more Null fields.
  4. #3
  5. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    Fields that are blank and Fields that are null are two completely different things. You can search for blank Fields using "" as your criteria a Field that is null will not be displayed with this criteria. A blank record could be a field in which something was there and is now deleted. This field isn't null unless specifically set to null. To search for both try IsNull and "". In VBA I like to use: IF Field & "" = "" then .... this checks against both null and blank fields.

    what do you mean trying to trim a blank Field. There's nothing to trim, unless I"m missing your meaning..
    Last edited by meratigoerr; December 19th, 2012 at 03:17 PM.
  6. #4
  7. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    Mostly agree with meratigoerr. However, deleting data from a field or bound control does result in Null. I do this all the time. I set up all my tables to not allow empty strings in any fields. AFAIK, only text/memo fields can have empty string.
    I like to know if my suggestion helped you. One way to let me know is by clicking star at bottom left corner of post.
    Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm
  8. #5
  9. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    Umm.. Yeah.. my bad. June you are correct.

Similar Threads

  1. Inserting NULL into blank fields in table
    By zeetec1 in forum .NET Development
    Replies: 13
    Last Post: June 24th, 2010, 07:05 AM
  2. How to check if table is null
    By ffleitas in forum Microsoft Access Help
    Replies: 3
    Last Post: May 9th, 2009, 03:16 PM
  3. Error checking for table fields null value constraint
    By ehsanking in forum Microsoft Access Help
    Replies: 1
    Last Post: August 17th, 2005, 06:18 AM
  4. Replies: 1
    Last Post: January 21st, 2005, 11:25 AM
  5. Replies: 6
    Last Post: September 23rd, 2004, 01:49 AM

IMN logo majestic logo threadwatch logo seochat tools logo