Problems with query and filters in database

  • Thread starter Thread starter Arnold Roth
  • Start date Start date
A

Arnold Roth

We have a medium sized database (about 350 records) that we
use to maintain status for requirements verification. This
morning I tried to run a simple query looking for a null
entry in one of the fields. I should have found
approximately 100. I found 1!

In trying to troubleshoot the problem I tried the same
thing with a filter with the same result (only found 1
entry). Tried the filter on another field. There are
approx. 250 null records. Found 5!

Tried looking thru with the find and replace dialog. This
would find the null records just fine but if I tried to
replace them with a new null, it came back with the error
message "Couldn't find the find what data" or something
similar.

Can anyone tell me what's happening? Please help. We use
this d/b constantly and now we're having trouble depending
on the results.

Thanks.
 
We have a medium sized database (about 350 records)

that we
use to maintain status for requirements verification. This
morning I tried to run a simple query looking for a null
entry in one of the fields. I should have found
approximately 100. I found 1!
Click to expand...

Could you post the actual SQL? Have you Compacted the database? Does
this field have its Allow Zero Length String property set to True? If
so, an empty string "" is NOT the same as NULL.
 
Yes, I did try to compact and repair the d/b but that had
no effect. Here's the SQL:

UPDATE [HIPS ver T] SET [HIPS ver T].[1205 RQMNT] = ""
WHERE ((([HIPS ver T].[ICD Parent REQID]) Is Null));


Appreciate the help.

Arnold
 
Yes, I did try to compact and repair the d/b but that had
no effect. Here's the SQL:

UPDATE [HIPS ver T] SET [HIPS ver T].[1205 RQMNT] = ""
WHERE ((([HIPS ver T].[ICD Parent REQID]) Is Null));
Click to expand...

Again... what is the value of the Allow Zero Length property on [1205
RQMNT] and [ICD Parent REQID]? For that matter, what are the datatypes
of these two fields?
 
They were both set to "No" (Allow Zero Length). That seems
to have solved the problem. Thanks. By the way, if the
"Required" attribute is set to no why should the "Allow
Zero Length" attribute not be set automatically to no?
It's a rather obvious conflict for the two to be set to
this combination.
-----Original Message-----
Yes, I did try to compact and repair the d/b but that had
no effect. Here's the SQL:

UPDATE [HIPS ver T] SET [HIPS ver T].[1205 RQMNT] = ""
WHERE ((([HIPS ver T].[ICD Parent REQID]) Is Null));
Click to expand...

Again... what is the value of the Allow Zero Length property on [1205
RQMNT] and [ICD Parent REQID]? For that matter, what are the datatypes
of these two fields?



.
Click to expand...
 
Some second thoughts on this question. First, these were
both memo fields. The query now works. However, the other
problem is still there. Trying to do a filter by selection
on another field where an empty entry is located shows a
very small number of records (like 5) where over 100 should
have been seen. The filter did not even pick up the record
that was selected for the filter. The fields selected for
these were text fields with zero length allowed. Can you
help with this?

Thanks again.
-----Original Message-----
Yes, I did try to compact and repair the d/b but that had
no effect. Here's the SQL:

UPDATE [HIPS ver T] SET [HIPS ver T].[1205 RQMNT] = ""
WHERE ((([HIPS ver T].[ICD Parent REQID]) Is Null));
Click to expand...

Again... what is the value of the Allow Zero Length property on [1205
RQMNT] and [ICD Parent REQID]? For that matter, what are the datatypes
of these two fields?



.
Click to expand...
 
They were both set to "No" (Allow Zero Length). That seems
to have solved the problem. Thanks. By the way, if the
"Required" attribute is set to no why should the "Allow
Zero Length" attribute not be set automatically to no?
It's a rather obvious conflict for the two to be set to
this combination.
Click to expand...

No, it is NOT a contradiction. NULL is a funny beast: a value that is
NULL means "This value is unknown, undefined; it could be anything". A
ZLS on the other hand means "This field has a very definite value: a
string of zero bytes length". The Required property of a field insists
that the field may not be NULL; but if ZLS is allowed, then the field
may indeed contain the known, definite value "".

For example, one could have a MiddleName field in a table. If the
middle name is NULL, this would mean "this person might or might not
have a middle name, if they do I don't know what it is"; if the middle
name is "" it would mean "This person does not have a middle name".
 
Some second thoughts on this question. First, these were
both memo fields. The query now works. However, the other
problem is still there. Trying to do a filter by selection
on another field where an empty entry is located shows a
very small number of records (like 5) where over 100 should
have been seen. The filter did not even pick up the record
that was selected for the filter. The fields selected for
these were text fields with zero length allowed. Can you
help with this?
Click to expand...

AGAIN... if you search using the criterion

IS NULL

you will fine those records where the field is NULL (unset,
unspecified, undefined). The text string "" will *not* be found,
because it is not NULL.

Try a criterion of

"" OR IS NULL

to cover both bases, or (as suggested earlier) set the Allow ZLS
property of the field to *no*.
 
John,

I hate to keep poking at this dead horse but how can I do a
"Filter by Selection" with a criterion? I would normally
point to a cell with the content I wish to look for and the
filter would show the similar records. This would need
either a "Filter by Form" or a query, right? If I
understand what you're saying about the ZLS then if I leave
that attribute at 'no', an empty cell is always a null. Is
that right?
 
I hate to keep poking at this dead horse but how can I do a
"Filter by Selection" with a criterion?
Click to expand...

I guess you can't!
I would normally
point to a cell with the content I wish to look for and the
filter would show the similar records. This would need
either a "Filter by Form" or a query, right? If I
understand what you're saying about the ZLS then if I leave
that attribute at 'no', an empty cell is always a null. Is
that right?
Click to expand...

That's just one reason I VERY rarely use zero length strings in
fields. They're confusing! Unless you have a *critical* reason to
distinguish NULL from "", I would suggest running an update query
searching for "" in the field, updating it to NULL, and then setting
Allow ZLS to False.
 
Back
Top