access query removes rows where the field is empty

Posted on 2011-10-10
Last Modified: 2012-08-13
for the location column: if I specify
Not Like 'X'

it will remove any row where location is X.  
But it will also remove rows where the location field is blank.

Since I didn't specify for it to remove the ones that are blank I would like to know how I can leave the rows where that field is blank
is null but that doesn't help.  "Not Like X" still removes the blank ones
Question by:vrosas_03
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)

    Try this:
    Not Like 'X' OR Is Null

    LVL 75

    Accepted Solution

    SELECT Table3.*
    FROM Table3
    WHERE (((Table3.location) Not Like "X" Or (Table3.location) Is Null));

    Author Closing Comment

    Seems like it works if it's on the same line but I still don't understand why I should have to include what I never asked to be removed.   I would consider this to be a bug.  I guess I've been removing rows based on the other fields wherever there are blanks simply by using "Not Like".  I had to put it in the same line in the same criteria slot in the editor.
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    Well, it's not a bug ... just how it works.  I forget at the moment exactly why.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now