Access inconsistencies

  • Thread starter Thread starter Hoopie
  • Start date Start date
H

Hoopie

Hi,

I wrote a simple query in Access VBA that has been working
for several months. Suddenly, it stopped working
yesterday. It went something like this:
SELECT *
FROM Table
Where field1 = FALSE and field2 <> 'address not found'

I wrote that from memory, so no need to look too closely
at syntax. The point is that it worked for several months
without a problem.

I copied the query into a new query in SQL View and it
wouldn't work there either. In order to get it to work, I
had to change "field2 <> 'address not found'" to "field2
IS NULL"

I feel like I'm writing code on a platform of shifting
sand. How does a query work for so long and then suddenly
stop?
 
A Null is different from a zero-length string (or any other string), and
this is an important distinction.

If the field contains zero-length strings as well as nulls, then:
Field2 <> 'address not found'
matches the zero-length strings, but not the nulls. But:
Field2 Is Null
will matches the nulls, but not the zero-length strings.

To get both, you need:
(Field2 Is Null) OR (Field2 <> 'address not found')

The best solution is to disallow zero-length strings in your tables, so you
don't have to constantly handle both. You do that by setting the Allow Zero
Length string to No for all your text fields, memos, and hyperlink type
fields.

For more information on handling nulls (including an explanation of why the
string match does not retrieve nulls), see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html
 
I wrote a simple query in Access VBA that has been working
for several months. Suddenly, it stopped working
yesterday. It went something like this:
SELECT *
FROM Table
Where field1 = FALSE and field2 <> 'address not found'

I wrote that from memory, so no need to look too closely
at syntax. The point is that it worked for several months
without a problem.

I copied the query into a new query in SQL View and it
wouldn't work there either. In order to get it to work, I
had to change "field2 <> 'address not found'" to "field2
IS NULL"

I feel like I'm writing code on a platform of shifting
sand. How does a query work for so long and then suddenly
stop?
Click to expand...

Hi Hoopie,

It wouldn't be a Microsoft product if you did not
feel the ground shifting below you. 8-)

In this case though, I think it is just the data that
"shifted" and a gotcha using "<>" in a WHERE clause.

WHERE (f2 <> 'str')

If f2 is Null, the condition will evaluate to Null,
which in the "WHERE world" is the equivalent of
"Not True," so the record is not returned.

You might see a "blank" which we know is
not equal to 'str', but the WHERE clause sees

"<first condition> AND Null"

so does not return record.

Check a backup and see if you previously
(when it worked) had any Nulls in field2.

There is a clever workaround that will work
exactly like you want it to work:

Where
field1 = FALSE
and
NZ(field2 <> 'address not found',-1);

Please respond if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter

There are several gotchas working with Nulls,
so I started out keeping the following "cheat sheet":

////////////////////////////////////////////
AND:

True AND Null = Null
False AND Null = False
Null AND Null = Null

////////////////////////////////////////////
OR:

True OR Null = True
False OR Null = Null
Null OR Null = Null

////////////////////////////////////////////
Expr1 {conditional operator} Expr2:

Result is Null if either (or both) expression is Null

(Null > 0) ;result = Null
(Expr1 = Null) ;result = Null *** cannot test if an expression is Null,
must use
IsNull(Expr1)
(Expr1 <> Null) ;result = Null *** use Not IsNull(Expr1)
(Null = Null ) ;result = Null

When a conditional expression evaluates to Null,
it is technically "Not True" as far as If/Then and
WHERE clauses are concerned, so the effect is
the same as if had returned FALSE.

query gotcha:
WHERE Expr1/Field1 <> Expr2/Field2

this will fail to return record if either is Null
(they might not be "equal",
but condition will evaluate to "Not True")

Workaround:
WHERE NZ(Expr1/Field1 <> Expr2/Field2, -1);

NOTE: this will also return record when both are Null!

////////////////////////////////////////////
Concatenation of strings:

Str1 + Null = Null "+" propagates Nulls
Str1 & Null = Str1 "&" treats Null as a zero-length string

////////////////////////////////////////////
If {condition} then :

When a condition evaluates to Null, treated as evaluating to False

"If (Null) then" is same as "If False then"

Pos = InStr( Str1, " ") Pos = Null if Str1 is null
If Pos > 0 Then when Pos = Null, evaluates to "If False Then"
'do something so won't do something with Null string


Because "&" treats Null as zero-length string,
the following condition is TRUE for Null and ZLS

If Len(Trim(Str1 & "")) = 0 then
'do something WILL "do something" if Str1 is Null, ZLS, or all spaces

AField = Null
?Len(Trim(AField & "")) = 0
True

Another method (TRUE for Null only):

If Len(Str1 & vbNullString) = 0 then

/////////////////////////////////////////////
Some Functions: ("error" = invalid use of Null)

Len(Null) = Null
Rnd(Null) --> error
CStr(Null) --> error
CDec(Null) --> error
workaround
-- use CDec(somefield & 0) or CDec(Nz(somefield,0))
Trim(Null) = Null
StrConv(Null,1)= Null
Left(Null,1) = Null
Rnd(Null) --> error
IsNumeric(Null) = False
IsDate(Null) = False
Asc(Null) --> error
Replace(Null,".","",1,-1,1) -->error

InStr(Null, Str1) = Null
InStr(Str1, Null) = Null
InStr(Null, Str1, Str2) if Start is Null, get Error

?datediff("yyyy",null,date())
Null
?datediff("yyyy",null,date())+ Int(format(date(),"mmdd"))<format(null,"mmdd")
Null

////////////////////
to create a new Null field in a maketable query

SELECT
IIf(-1,Null,"") AS 255CharNull,
IIf(-1,Null,0) AS LongNull,
Null AS BinaryNull
INTO testnull
FROM tbl1;
 
Back
Top