Hi,
Should not, since in both cases, it should return NULL :
? eval( " NULL BETWEEN 10 AND 20" ), eval(" NOT NULL BETWEEN 10 AND
20")
Null Null
Probably a problem of logic. Are you using the ALL universal qualifier?
.... WHERE f1 >= ALL( SELECT f2 FROM table2)
is NOT the same as
.... WHERE f1 = ( SELECT MAX(f2) FROM table2)
in cases f2 has NULL values. Indeed, MAX removes the NULL, by convention,
returning the max of the not null values, but ALL does not removes NULL. If
there is a NULL, among the possible values for f2, then we will test, at
some point, f1 >= NULL, which returns NULL and so, ALL fails to return
TRUE.
You probably have some case of weird NULL logic implied in your SQL
statement... but not with BETWEEN itself. Can you share your (simplified)
SQL statement with us?
Hoping it may help,
Vanderghast, Access MVP
Janet said:
I am searching for records that might have a range of codes in any of 10
fields. When I use, for example, Between 480 and 490 all records that have
a value in that range are returned, even where some of the fields are blank
or null. But when I use Not Between 480 and 490, only records with values
in all fields are returned. Is there an explanation for this? Now that I
know it, I can write the query to read Between 480 and 490 or is null but I
wouldn't have known to do this if I did not know what was in the source
data.
