a field is simultaneously not Null, and not not Null

[Cottonshirt]

I have a SELECT query, let's call it query_A, that returns 15075 records



one of the fields, a text field, in query_A is called realm

I run a second query, using query_A as the dataset, with the condition: Where query_A.realm Is Not Null
this returns 14075 records

then I run a third query, using query_A as the dataset, with the condition: Where query_A.realm Is Null
this returns 498 records

but 14075 + 498 = 15073, which mean there are two records missing

this suggests that query_A has two records that are not Null, but that are also not not Null

I have checked the joins are the same, the join types are the same, I even deleted the third query and simply copy pasted the second query and altered the criterion from Is Null to Is Not Null. and none of this made any difference.

any ideas as to what I should be looking for to locate these two records?



many thanks,

Cottonshirt

[Minty]

You could try using

WHERE YOUR_ID FIELD Not IN(SELECT Your_ID Field FROM query_2) AND YOUR_ID FIELD Not In ((SELECT Your_ID Field FROM query_3)

[moke123]

but 14075 + 498 = 15073, which mean there are two records missing

14075 + 498 = 15073 ? (14,573)

what about ZLS's or non printable characters?

one of the fields, a text field, in query_A is called realm

what happens when realm is the only field in the query?

[Cottonshirt]

moke123 said:

what about ZLS's or non printable characters?

I ran a query asking for Len(realm), and this returned 15075 records with a minimum value of 5

but I hadn't thought of this, it is a good suggestion, thank you.

what happens when realm is the only field in the query?

this turns out to be decisive.

if realm is the only field in the query, then WHERE (realm is not null) returns 15075 records, and WHERE (realm is Null) returns zero records.

which points out the glaringly obvious fact that Null in this scenario doesn't mean, "the field has no value" it actually means, "return records that do not meet the conditions implied by the joins." no Nulls are actually involved.

this means that 14075 records matched records in tbl_realm, and 498 records did not match tbl_realm, which still left me a problem, but not the same one I started with.

Minty said:

You could try using

WHERE YOUR_ID FIELD Not IN(SELECT...

and I asked myself, "What ID_Field?"

I hadn't included a record ID in the query because that data was only incidental to my purpose. when I added the record ID to the query the discrepancy disappeared.

WHERE realm is not Null now returns 14577 records,
and WHERE realm is Null still returns 498 records

so 14577 + 498 = 15075 and everything is accounted for.


thank you both very much indeed for your help.


Cottonshirt

[CJ_London]

you have resolution but for the future, if you have a query which isn't working as you expect, then post the sql to your query. It may be that you are using DISTINCT or GROUP BY or you need to use left joins