Query not responding: empty fields?

[adrian.stock22]

Query not responding: empty fields?

I have a table with a field 'Fax number', type: text (since occasionally we write a comment in there, like 'prohibited').

Some records have fax numbers, others are empty.

I want to find all records which do have a fax number. So I wrote into the Query: "is not null", expecting to get only the records which have a fax number or some text in them.

In fact, all records came up in the query, empty as well as non-empty fax fields.

I was wondering if the 'empty field' had a blank space in them, but could not find any. Tried backspace key, but there was nothing to backspace on.

I used the find-replace utility and searched for single space in Whole Field. It picked out quite a few records, but not all - so something invisible seems to be there.

However, when I opened the 'replace' window of find/replace, and had the replace window empty, then clicked 'replace', the msg came 'Access cannot find the specified text'.

What am I doing wrong? What do I have to do to get the query to work?

Thanks,

Adrian

 

[Michael J Ross]

Try <>"" as your criteria?

 

[workmad3]

An empty field is different from a null field, especially with strings. An empty string is a string with no characters, except for the string terminator character (which you can't see). A null string is a string with absolutely no data in, not even the terminator.

Best thing to try is to make sure the field isn't null and also isn't equal to the empty string. Checking for that should be possible with the following in the SQL where clause
" WHERE Faxnumber IS NOT NULL AND NOT FAXNUMBER = ''"

 

[adrian.stock22]

Hi, thank you, Michael. That worked.

I should still be most interested to know WHY my original approach did not worked: is not null

What is the difference between: is not null, and
<>""

Are there characters in my field which I should remove before creating the query?

Thanks.

Adrian

 

[neileg]

Nulls and zero length strings are not the same. Access help covers this, so have a look. You can set the field properties to allow ZLS or not. If you don't allow zero length, you will always have a null in an empty field. Otherwise you can enter text in a null field and delete it and it becomes a ZLS not a null.