Is Null Not working, But does work if Change to <1?

[Jarad]

I have a query, and it has been working just fine, till today. I have it based on a field being null. This field shows null in the table, it is a text field but it is a 19 digit number if there is anything in there. The fields didnt come up in the query when it was based on Is Null. But if I changed it to a <1 they came up just fine. and After further testing, the change works just how I had it working with the Is Null or so I thought. Any ideas on the WHY behind this, I am really curious about it, thought I would get some opinions on why this happened.

 

[neileg]

Has anything happend to change the null values to zero length strings?

 

[Alc]

If it's a text field, sometimes what looks like a null is a zero length string. You could try
IF IsNull() OR IF <> ""
and see if that picks it up.

 

[Jarad]

Nothing should have changed, this is a VERY static database, extremely locked down, information is pulled from linked SQL tables, access forms are basically just a front end for us. But that makes sense, just strange that out of 206000 records, only those 3 in that particular load wont pull in correctly.

 

[KenHigg]

a. If you're going to test with <1 then shouldn't this be a numeric field.
b. When you say they didn't come up in the query are you saying that with a criteria of 'Is Null' on the column no values show up even if there are values in some rows?

 

[Jarad]

a. If you're going to test with <1 then shouldn't this be a numeric field.
b. When you say they didn't come up in the query are you saying that with a criteria of 'Is Null' on the column no values show up even if there are values in some rows?

I would think it should be a numeric field! That is why I originally started the criteria to be Is Null, and not based on <1.

No results if the query is left to be based on the fields being null. If I change it to the fields being based on <1, I get the desired results, even though it is a text field.

 

[KenHigg]

I don't mean to beat a dead horse...

If a field has a value and your criteria for the coulmn is 'Is Null', then then you get nothing?

 

[Jarad]

I don't mean to beat a dead horse...

If a field has a value and your criteria for the coulmn is 'Is Null', then then you get nothing?

I should be getting 3 results. The results should be shown if the Field 'Is Null'. If the field has something in it, I dont want to see those Accounts on my query.

 

[Banana]

As one poster pointed out, I'd look at your tables to see if it allows a zero-length string, which isn't same thing as Null.

ZLS= "" (You may not always see the "" in the records)

Null = Null

"" <> Null