# MS Access Query not returning blank fields



## mariaa33 (Aug 7, 2008)

I have a query that has a couple of text fields where in the criteria I put <>"Power". When I put this criteria in, it also does not show me records that the field is blank. If I leave the criteria empty, it shows me all records including the records with the blank fields. How do I get the query to exclude the criteria Power but still show records where that field is blank?


----------



## OBP (Mar 8, 2005)

How many Tables in the query?


----------



## Albantar (Dec 9, 2008)

Try adding something like:

OR ... = ""


----------



## mariaa33 (Aug 7, 2008)

I have 4 tables and when I try or ="" I get even less records


----------



## OBP (Mar 8, 2005)

Yes you would. You may not be getting the Null Fields due to the Join Settings between the Tables.
Can you post a Screenshot of the Query in Design View?


----------



## slurpee55 (Oct 20, 2004)

Set 2 criteria
Is Null
and below it in the Or line
<>"Power"
(You can reverse the order, just have them both in.)


----------



## OBP (Mar 8, 2005)

slurpee, that does the same as Albantar's suggestion, it excludes everything else but Is Null.


----------



## mariaa33 (Aug 7, 2008)

I tried that but it pulls everything even outside my date criteria. I think I am just going to dump it to excel and use a macro to delete the lines that contain Power. Thanks.


----------



## OBP (Mar 8, 2005)

mariaa, you realy should learn how to do this in Access, can you post a Screenshot of the Query in design mode?


----------



## mariaa33 (Aug 7, 2008)

I have attached my design view but it actually has 7 tables 4 are linked to ODBC and 3 regular tables.


----------



## OBP (Mar 8, 2005)

OK can you click on the line that Joins the Outage Table to the RootCause Table and tell me what "Option" has been selected please?


----------



## OBP (Mar 8, 2005)

maria, that looks like an 8D Problem Solving Database?


----------



## mariaa33 (Aug 7, 2008)

all outage and only those in root cause where join fields are equal. I don't know what a 8D DB is.


----------



## OBP (Mar 8, 2005)

OK, can I assume then that the Root Cause Table does not have a "Null Value" field?
From your previous remarks do you want to delete Records that do not have "Power" or No value in the Root Cause Field?
8D is a Problem Solving Technique.


----------



## mariaa33 (Aug 7, 2008)

Basically I need any field that is not Power but include blank fields. So I should have records that have Card replaced, shelf replaced, <blank>, etc. just not Power.


----------



## mariaa33 (Aug 7, 2008)

And I am not deleting any records just pulling a query


----------



## OBP (Mar 8, 2005)

OK, Delete the Root Cause Table and tell me if that gives you what you want.
If it does Create another Query based on your Current query and add the Root Cause Table to that one to pick up the Root Cause Names.


----------



## mariaa33 (Aug 7, 2008)

So the Root Cause now does not show up in the first query. The root cause before deletion is linked to the outage table to rootcause_value field which is not in my query so I have no way to link the new query to pull the rootcause.


----------



## slurpee55 (Oct 20, 2004)

OBP said:


> slurpee, that does the same as Albantar's suggestion, it excludes everything else but Is Null.


I did it and it worked for me OBP. I had 15 items, 3 were blank and 5 were "Power." In the query I had 10 items - the 3 blank and the others,
So something is odd....


----------



## slurpee55 (Oct 20, 2004)

So, you could do a query first on the table ARADMIN BONS SYSTEM (if I read it right) and do the filtering in that query, then use that query in place of the table in the second main query.


----------



## OBP (Mar 8, 2005)

In the Query, Filter out the Power Records using the Root Cause ID (Root Cause Value) that represents "Power" in the Root Cause table, there must be a field that makes the Link, use that.
Then add back the Root cause Table in a second query.


----------



## OBP (Mar 8, 2005)

Slurpee, use Is Not Null and it will work the they want.


----------



## slurpee55 (Oct 20, 2004)

mariaa33 said:


> I have a query that has a couple of text fields where in the criteria I put <>"Power". When I put this criteria in, it also does not show me records that the field is blank. If I leave the criteria empty, it shows me all records including the records with the blank fields. How do I get the query to exclude the criteria Power but still show records where that field is blank?





OBP said:


> Slurpee, use Is Not Null and it will work the they want.


OBP, Is Not Null will only show fields with data, as you know, of course. 
In Maria's first post (see above) she wants to "still show records where that field is blank."
Or am I missing something because of the complexity of the query?


----------



## OBP (Mar 8, 2005)

slurpee, you are correct, I was in a hurry last night. The Is Null SHOULD work, but and it is a very big but, the blanks should not be missing in the first place.
When Maria puts in <>"RT AC Power" it should ONLY exclude those records with RT AC Power as the Root Cause, but it is excluding nulls as well. It was that inconsistency that prompted her Thread in the first place. It is also why I asked for the Join to the Root Coasue Table, but that Join should not exclude Nulls either. Which is why I wanted her to try excluding the RT AC Power with the Root Cause table out of the Query to establish if the Table was causing it.
But I have realised that if it is the Table it may still not be possible to reconnect it in a second query and not lose the Nulls if the same problem recurrs.
There is a very good work around which is to have a Root cause called "Not Yet Established" and replace all the Null (blank) records with that (and make it the default entry for that field). This would then provide a valid Join to the Root Cause Table.
I would obviously like to work with the actual Database as we can't reproduce what Maria has.


----------



## slurpee55 (Oct 20, 2004)

Yes, I understand what you are saying OBP, but given the complexity of Maria's db and the fact that it is linked to outside (ODBC) sources, you probably can't ever manage to see a functioning copy of it.
If the Root Cause table is actually the cause of this odd behaviour (ach, too many years spent in Canada ruined my spelling) then it should also show up in a simple query of just that table. If the nulls show in that simple query, then I would use it instead of the table in the more complex query.
If Maria does that and still doesn't see the null values, then at least we have eliminated that table and probably need to look at the others that have filtering criteria in the overall query.


----------



## OBP (Mar 8, 2005)

I agree we will just have to wait and see if he comes back to us.


----------

