Filtering "is not null" returns null values !!

[Ross_curtis]

I have a table which has been imported from several Excel sheets, in total I have approx 142000 records.
One of the columns is status - which I am trying to base a filter on. IE filter all Status 1's, then I need to find which records have a value in a the payroll column. From my limited experience it should be easy: Select filter by form enter "1" in the status column and "is not null" in Payroll. Done.
However, my search is showing both null and not null values. IE it's treating some cells as having a value when there are none (there are no blank spaces either). I have ran a trim update but I am getting the same results.

Also, if I do a similar search using a query I get the same results.

Any ideas?

Thanks
Ross

 

[DCrake]

The fields may appear to be null but they could be empty there is a difference between Null and Empty. Test for the length of the field and run it based on 0 length.

David

 

[WIS]

I have a table which has been imported from several Excel sheets, in total I have approx 142000 records.
One of the columns is status - which I am trying to base a filter on. IE filter all Status 1's, then I need to find which records have a value in a the payroll column. From my limited experience it should be easy: Select filter by form enter "1" in the status column and "is not null" in Payroll. Done.
However, my search is showing both null and not null values. IE it's treating some cells as having a value when there are none (there are no blank spaces either). I have ran a trim update but I am getting the same results.

Also, if I do a similar search using a query I get the same results.

Any ideas?

Thanks
Ross

What is the Data Type for both those flds.

 

[Ross_curtis]

Both are text.

 

[namliam]

I agree with David, they are empty fields not blank. Small but important difference...

Test your field to be
Is not Null and <> ""

 

[Ross_curtis]

Thanks for the tips.

It certainly makes sense. Should I want to convert a null field to a empty field and visa versa how would I go about that?

 

[namliam]

Use an update query, but why would you want to??

Possibly using the NZ function in a query will convert any null value to an empty string:
NZ(Yourfield, "")
or to a zero
NZ(Yourfield, 0)

 

[Ross_curtis]

The reason I'd want to is that I seem to have a mixture of Null and empty - for some reason.

I'll give that a go, thanks. Having said that using <>"" as a search seems to work perfectly so I should be able to live with it.

 

[namliam]

Anytime you want this you simply have to consider if Null = "" in this case...
And if that is true the Is Null and <> ""

This is a basic check that is used constantly!

 

[namliam]

Oh and the empty strings, as to where they come from? That is from users entering something and then deleting it again (I think) not 100% sure though...

 

[gemma-the-husky]

dealing with nulls is never easy - as there is no way of telling whether a field is blank (ie null) or is just a zero-length-string (obviously this applies to text fields only)

one thing you could do in the table, is make the field required- in which case a blank value (null or zls) will not be allowed, but this depends on your app.

the other thing you can do is test for both in your query - eg in the criteria use

is not null and <>""

another way is to force it to a value in the field definition with the nz function

nz(myfield,"")

 

[Atomic Shrimp]

Another way around it is to set the default value for the fields to "" - then new records will contain empty strings, not nulls.

(You'd have to do a one-off update too, to convert all the existing nulls.)

Whether or not this is really necessary is up to you - there's nothing horribly wrong with having a mixture of nulls and blanks, but it's an extra thing to have to think about, so it's not a crime to get rid of the nulls either.