Is null vs. does not equal "X"

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

Guest

I have some Access queries where I append data based on various flags that
have been created. Based on the flags, I append the data in a table if there
is no "X" in the flag column. However, I have noticed that when I specify
"Is Null" in the criteria, I get different results than using <> "X" or not
"X".

I'm unsure of which one to use as sometimes "Is Null" gives me the results I
want while <> "X" does as well. In general, it seems "Is Null" works better,
but it doesn't seem to always give me the data that does not have an X flag
in the flag column.

Is there a difference between these two operators? Any suggestions on which
one is better to use? All the flag columns are text fields with a field size
of 1.

Thanks in advance,
Matt
 
Yes Matt, their IS a difference, albeit a small one. The "Is Null" operand
only returns true given two conditions;
Firstly, the variable in question must be of type 'Variant'
Secondly, there must be no data in said variable.

So the one reason that one would return something whilst the other would
not, is that there is actually data within the column, perhaps a space. This
would be returned in a <> "x", however, the "Is Null' Would not pick this up.

It could also be an issue with the type of variable you are using, make sure
that you are using a variant.

Its probably best to use a string, using <> x. Variants require more memory
methinks, or processing, since Visual Basic will try to find a format for
them that will fit best.
 
Back
Top