Field appears empty but isn't?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Matt D Francis - 12 Apr 2006 14:48 GMT

I have a text field in a database where several rows are apparantly empty.
However when I try and apply criteria to a Query to return only these rows, I
can't get them. All of the following return 0 rows:

IsNull
IsEmpty
IsMissing
""

First of all what is are the differences between the above functions?

Second - any idea on what could be in my field, and how to pick them out?

If I apply a "Filter By Selection" filter on one of blank fields, it works,
but I need to develop a Query with this is my main condition.

???

Reply to this Message

Matt D Francis - 12 Apr 2006 15:01 GMT

OK, I've just had it pointed out to me that I need a space if I'm using it in
the Query Grid =

Is Null

.....

But my question stands - what are the differences in the ones I've listed?

> I have a text field in a database where several rows are apparantly empty.
> However when I try and apply criteria to a Query to return only these rows, I
[quoted text clipped - 13 lines]
>
> ???

Reply to this Message

Tom Ellison - 12 Apr 2006 15:01 GMT

Dear Matt:

Your column of data could have values in it that are one space, two spaces,
or any number of spaces. They are difficult to distinguish. However, you
should be able to move a cursor through this and count them.

If I had to "pick them out" I'd start by eliminating everything else.
Perhaps you could use:

[YourColumn] NOT LIKE "*[A-Z0-9]*"

See what you have left. Expand the criterion above to eliminate any other
thing you can see.

Tom Ellison

>I have a text field in a database where several rows are apparantly empty.
> However when I try and apply criteria to a Query to return only these
[quoted text clipped - 15 lines]
>
> ???

Reply to this Message

Matt D Francis - 12 Apr 2006 16:44 GMT

OK so that eliminates any possible characters?
Thanks, but maybe you missed my second post - they are in fact Null, I'd
just used the incorrect syntax (missed a space ironically)

But I'd still like an explanation of the different functions I listed if
anyone has the time? Can't find anything on Access Help.

> Dear Matt:
>
[quoted text clipped - 31 lines]
> >
> > ???

Reply to this Message

Tom Ellison - 12 Apr 2006 17:01 GMT

Dear Matt:

If you look at the time of our posts, it is obvious that I did miss your
second one.

IsMissing refers to the situation where an optional parameter is not passed
to a function. This would have no relevance to a query.

IsEmpty also has no relevance to a query.

To see the details of these in help, choose the Contents tab, expand "Visual
Basic Language Reference" / "Functions" / "H-L" The topics are listed
within.

Tom Ellison

> OK so that eliminates any possible characters?
> Thanks, but maybe you missed my second post - they are in fact Null, I'd
[quoted text clipped - 43 lines]
>> >
>> > ???

Reply to this Message

Matt D Francis - 13 Apr 2006 09:39 GMT

OK, that's useful, thank-you.

> Dear Matt:
>
[quoted text clipped - 59 lines]
> >> >
> >> > ???

Reply to this Message