"Is not Null" calling up blank fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

For the vast majority of you this is probably quite a simple question to
answer but forgive me - I'm a novice at this game!

I really need your help on a query - very straightforward; I want to call up
all the records in my database where I have entered information into the
field called "Market Intelligence". This is information I only collect now
again and so many of the records will/should be blank and therefore not
needed in my report.
Currently when I produce a report (using this query) it brings up records
which are blank (and I have checked... there is nothing there!) as well as
records that have required information in the field. The criteria I have
used is: "Is not null" assuming that should select records where the field is
not empty... but not the case. Is this the correct criteria term to use?

Anyone any ideas - I'd really appreciate your support.

Thanks :-)
 
I have had this problem too. When it has cropped up, using >0 has usually
provided the results I was expecting.

Ed G
 
MNicholas said:
For the vast majority of you this is probably quite a simple question to
answer but forgive me - I'm a novice at this game!

I really need your help on a query - very straightforward; I want to call up
all the records in my database where I have entered information into the
field called "Market Intelligence". This is information I only collect now
again and so many of the records will/should be blank and therefore not
needed in my report.
Currently when I produce a report (using this query) it brings up records
which are blank (and I have checked... there is nothing there!) as well as
records that have required information in the field. The criteria I have
used is: "Is not null" assuming that should select records where the field is
not empty... but not the case. Is this the correct criteria term to use?

Anyone any ideas - I'd really appreciate your support.

Thanks :-)

What you need to bear in mind is that "Null" is not the same as a
zero-length string. "Null" means that the value in the field is undefined,
whereas a zero-length string IS defined: it's a zero-length string! When
testing a field or whatever which might be Null or might be a zero-legth
string, it's a useful trick to use the Nz function so that you only need to
test for zero-length string e.g.

...WHERE Nz(some_field) <> ""
 
Back
Top