Results 1 to 10 of 10
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Dealing With Blank Values In Query

    I am trying to exclude any blank/empty/null records from my query. If I simply add IS NULL as the criteria for the field the blank/empty row still returns.



    What other way do I have to exclude this row from my query since IS NULL is not working?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    To exclude Null values I think you need criteria as:
    Not Is Null

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    maybe I explained poorly. I'm wanting to isolate all the null values so I can run a delete query and remove them from the table. But using IS NULL does not show any blank rows even tho there is one

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    is null applies to a specified column, not an entire row. With a couple of possible exceptions every table will have a primary key which cannot be null. So suspect there is something you aren't telling us.

    Suggest provide a screenshot of your table showing the 'null row' and the sql to your delete query

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Could it be that the OP has a ZLS rather than a Null

  6. #6
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    I'm importing a .csv file into access so at this point we do not have a primary key assigned. I'm working on getting an image uploaded, but when I filter the table on the Address1 line access shows (Blanks) and if I filter on (Blanks) I see the row that I want to delete


    image here
    https://ibb.co/bNvLw0D

  7. #7
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    As Bob said, you might have a zero length string, can you try using "" in your criteria, or even better use it in a new field where you trim the address field in case there are any spaces: Select * From YourTable Where Trim ([Address 1]) = "";
    Cheers,

  8. #8
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by Gicu View Post
    As Bob said, you might have a zero length string, can you try using "" in your criteria, or even better use it in a new field where you trim the address field in case there are any spaces: Select * From YourTable Where Trim ([Address 1]) = "";
    Cheers,
    ah - that looks like it may be it. What would be the proper VBA Syntax for this?
    Code:
    DoCmd.RunSQL ("Delete Test1.[Address 1] From Test1 Where (((Test1.[Address 1])=""));")
    if I do a direct copy/paste I get the error
    Run-time error '3075':
    Syntax error in string in query expression '(((Test1.[Address 1])="));")

  9. #9
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Try
    Code:
    CurrentDb.Execute "Delete * From Test1 Where Trim([Address 1])=''; ", dbFailOnError ' note second to last are actually two single quotes
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    That got it. Thank you kindly

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dealing with Duplicates and Boolean Values
    By EcologyHelp in forum Access
    Replies: 6
    Last Post: 11-17-2015, 12:49 PM
  2. Query Blank Values
    By kgbo in forum Queries
    Replies: 2
    Last Post: 10-29-2013, 03:54 PM
  3. Dealing With Incoming Null values - Variants
    By CementCarver in forum Programming
    Replies: 5
    Last Post: 09-04-2013, 01:53 PM
  4. CrossTab Query - Blank Values
    By bullwinkle55423 in forum Queries
    Replies: 4
    Last Post: 07-17-2013, 01:59 PM
  5. Replies: 3
    Last Post: 01-20-2012, 04:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums