Field appears empty but isn't?

[Matt D Francis]

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.

???

 

[Matt D Francis]

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?

 

[Tom Ellison]

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

 

[Matt D Francis]

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:

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
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.

???

 

[Tom Ellison]

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
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:

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


message

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.

???

 

[Matt D Francis]

OK, that's useful, thank-you.

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
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:

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


message
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.

???