Is It Really Null?

  • Thread starter Thread starter PC User
  • Start date Start date
P

PC User

In using a filter criteria "Is Null" or "Is Not Null" I read in the
help for A2K that if the text or memo field has a space typed into it
and no other text, that the filter critera states that the field "Is
Not Null". I'm looking for a way to check for an empty text or memo
field that has no characters typed into it even though the user may
have accidently typed a space into it. That means that a text or memo
field may look empty, but may still contain a space. If so the "Is
Null" criteria doesn't help me.

In other words, I'm trying to build a critera that selects text or memo
fields that are empty of text even though the user may have typed a
space into it.

Thanks,
PC
 
PC user,
Try adding an AND condition to your Is/IsNot Null criteria
Not IsNull(YourField) and YourField <> " "

But...
Al's rule #622 in a series... "It's better to prevent problems than to
code around them." <grin>
I would use an Update query to find those fields that have just a space
in them amd Null them out and then try one of the following solutions.

A good solution would be to use the field's BeforeUpdate event to check
for a space, and "automagically" clear the space entry back to Null...
If YourField = " "
Cancel = True
YourField.Undo
End if

Or, you could use the ValidationRule for the field to not allow a space.
<>" "
You might even try to catch 2 or 3 spaces just to be sure...
<>" " or <>" " or <>" "
With a ValidationText of...
"Fields can not contain just a space/s. Please delete... etc.." or
something like that.
 
It's actually more difficult than that. There's also an empty string which is
no text at all, not even a space, but not null. Try this to cover all the
bases:
Is Null Or " " Or ""

Actually I think just Is Null Or " " will work including finding some
non-printable ASCII characters.
 
PC User said:
In using a filter criteria "Is Null" or "Is Not Null" I read in the
help for A2K that if the text or memo field has a space typed into it
and no other text, that the filter critera states that the field "Is
Not Null". I'm looking for a way to check for an empty text or memo
field that has no characters typed into it even though the user may
have accidently typed a space into it. That means that a text or memo
field may look empty, but may still contain a space. If so the "Is
Null" criteria doesn't help me.

In other words, I'm trying to build a critera that selects text or
memo fields that are empty of text even though the user may have
typed a space into it.
Click to expand...

Actually, just typing a space into a text box will not normally store a
space in the field, because Access will trim trailing spaces from the
control's text and then interpret the resulting zero-length string as
Null. So this isn't as big a problem as you may think. Normally, the
only ways to get a simple spaces into a field without any nonblank text
(assuming you allow the control to finish updating its value before you
check it) are either to import data into the table or to assign the
value in code. You *can* get a zero-length string into the field
manually by typing "".

If it's really a problem in your application and you must ensure that
the field has *something* in it, not Null nor spaces nor a zero-length
string, you can do this in a query by checking only two cases: Null and
"" (zero-length string). That's because, to the database engine, blank
is equal to blank, whether it's 1 space or 100, or a string with no
characters at all. This doesn't work in VBA, where "" is not equal to "
", which is not equal to " ", but it does work in queries.

You can test for both cases in one go by using the concatenation
operator to convert Nulls to zero-length strings:

WHERE ([FieldName] & "") = ""
 
PC User said:
In using a filter criteria "Is Null" or "Is Not Null" I read in the
help for A2K that if the text or memo field has a space typed into it
and no other text, that the filter critera states that the field "Is
Not Null". I'm looking for a way to check for an empty text or memo
field that has no characters typed into it even though the user may
have accidently typed a space into it. That means that a text or memo
field may look empty, but may still contain a space. If so the "Is
Null" criteria doesn't help me.

In other words, I'm trying to build a critera that selects text or memo
fields that are empty of text even though the user may have typed a
space into it.

Thanks,
PC
Click to expand...


You might want to use a statement like:

Len(Trim(Nz([YourField],"")))=0

This will be true for Nulls, blank strings(""), and strings consisting of
any number of spaces.





--
 
Back
Top