Null values getting picked up in Query

[SpentGeezer]

Hi,

I have a large questionaire table. One column has yes/no/na as options. I was testing it and set some records to NA then went through and deleted the NA.

Now I have a query --> Select * From Table where answer not = "NA". This query is leaving out the records where I had NA and then cleared them. They are clearly nulls, but the query is picking them up as if they were NA still.

This is doing my head in and probably sounds like rubbish, but if anyone knows why this may be occuring, please enlighten me.

Thanks.

 

[SpentGeezer]

UPDATE: if I put is null in the query instead of is not = "NA" then I get all the records that I had been testing (typed in NA and then deleted it). If I query for is not null, I get all the blank records that I have not tested (which should be nulls right?)

 

[The_Doc_Man]

Probably have an OUTER JOIN involved. If that is necessary, then you need to consider some issues about Boolean or Yes/No data types.

You are doing tri-state cases - Yes, No, and No Answer or Not Available (whichever you meant for it). True Yes/No rules would say that you cannot use a Y/N type for three states. (Yes, there is such a thing as tri-state for a Y/N type, but I abhor it.)

Normally I'm a pragmatist, but for this case I'm a known purist precisely because of the big headaches caused by NULL in various tables and queries. IMHO you should never use a Yes/No data type for tri-state responses. What do you do if you suddenly find that you NEED to distinguish between Yes, No, No Answer, and some fourth state such as "partial" or "maybe" or something like that? You suddenly can't store your results.

Search this forum for many discussions about NULL handling, particularly in queries.

 

[SpentGeezer]

It is a text field, not Boolean (Yes/No).

 

[boblarson]

Just deleting text from a text field doesn't necessarily make it NULL. It can actually be an Empty String (unless you set the Allow Empty Strings property to NO in the table at the field level).