Find solution
How to convert my SQL query to MS Access query?

MS Access 2007 query filter "is null" stopped working

  • Been using the query filter "is null" for years, this version Access & previous, with no problems: always returned all records where the specified field was blank. Suddenly, it stopped working in the query I'm currently working in, and in others (have not checked all). Have tried deleting filter, saving query, re-entering filter & saving - no luck. rebuilding this query from scratch would be a major headache. Any ideas?

  • Answer:

    You will have to be more specific about the records the query now returns. "Blank" can mean null, or it can mean empty string, which is a string with 0 characters, which is different than null. You cannot just delete the data in the field to make it null. To insert a null directly into the table, you must use ctrl 0. Add more description if you want more help. I hope this helps.

kittyshrink at Amazon Askville Visit the source

Was this solution helpful to you?

Other answers

The field is a text field. I want the query to filter out records that have text in that field, keep those that have NO text (are left blank - that is, skipped - on input). "Is Null" used to work (have been using this database/query in Access for years). This problem didn't appear until recently. I have a PC running Windows 7 64-bit and using Access 2007. I don't believe the problem began with the Windows 7 or Access 2007 upgrades, but can't be entirely sure, as I don't use this db regularly. I also have no training in database use, just years of hands-on experience so don't understand a lot of the language: Does your explanation mean I need to type CTRL-0 for every text field in a record that I want to remain empty but still be searchable? (what a pain!) I thought that only applied to numerical fields? Thanks for the help (past & in advance).

kittyshrink

No, don't change the data. Alter your query to return both nulls and empty strings: where (FIELD is null or FIELD = '') It is possible that an upgrade changed null values to empty strings. Stranger things have happened in my IT career. I hope this helps.

Manimal

You can read, view, open mdb file due to special software below

danny276

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.