Not Between not working!

T

Todd W

I have a database setup with street names and valid address ranges
(tbl_segme). In another table, I have customer addresses (tbl_unpub). I
want to compare the addresses of the customers to ensure my street ranges are
correct. Each table has a text_id value that associates each unique street
name to a number. For example, Cherry Street may have text_id 1904, Main
Street a text_id of 8894, and so on. tbl_segme has address ranges broken out
into FRL, TOL, FRR, TOR. Those represent valid ranges From Left - To Left,
From Right - To Right.

In tbl_unpub, I have the following records:
1 WINDMILL LN
23 WINDMILL LN
154 WINDMILL LN
177 WINDMILL LN

In tbl_segme, I have 4 records for WINDMILL LN with the following ranges:
FRL value of 28, TOL value of 68, FRR value of 23, TOR value of 69
FRL value of 72, TOL value of 112, FRR value of 87, TOR value of 107
FRL value of 118, TOL value of 118, FRR value of 121, TOR value of 121
FRL value of 131, TOL value of 185, FRR value of 140, TOR value of 186

Text_id values are equal for tbl_unpub and tbl_segme.

According to those entries in tbl_segme, I should only have 1 row in my
tbl_temp for 1 WINDMILL LN. However, when I run the query below, I end up
will all 4 records from tbl_unpub.

SELECT DISTINCTROW TBL_UNPUB.*, TBL_UNPUB.addr INTO TEMP_TBL3
FROM TBL_UNPUB INNER JOIN tbl_segme ON TBL_UNPUB.text_id = tbl_segme.TEXT_ID
WHERE (((TBL_UNPUB.addr) Not Between ([tbl_segme].[frl]) And
([tbl_segme].[tol]) And (TBL_UNPUB.addr) Not Between ([tbl_segme].[frr]) And
([tbl_segme].[tor])) AND ((TBL_UNPUB.MUN)=([tbl_segme].[munl])));

Any ideas?

Thanks for your time and consideration!
Todd W
 
M

Michel Walsh

Hi,


Alphabetically, the following are in order: A, AZ, B,
so, alphabetically, the following are also: 1, 19, 2

ie. "19" is between "1" and "2", as "AZ" is between "A" and "B"
NUMERICALLY, 19 is not between 1 and 2, but alphabetically, definitively,
but "19" is between "1" and "2".

So, what is used? Depends on the context. Since you are using strings,
***alphabetical*** order is considered, in your case!

IF the number is always in front of the string, you can revert to numerical
ordering using VAL:


...... WHERE val( address1 ) BETWEEN val( address2) AND val( address3)


It may be easier to make some tests in the Debug Immediate Window:

? val(" 177 WINDMILL LN")
177

and

? 2 > val(" 177 WINDMILL LN")
False


while

? "2" > "177 WINDMILL LN"
True

and

? " 2" > "177 WINDMILL LN"
False



The latest two are almost the same, almost, but the result is as different
as it can....since the space also has its importance, when alphanumerical
ordering is used.


Hoping it may help,
Vanderghast, Access MVP
 
T

Todd W

Thanks for the reply, Vanderghast. Great lesson, too!

I neglected to mention that the address number is in a separate column
of tbl_unpub. In this case, it's contained in tbl_unpub.addr.

I have tbl_unpub.add, tbl_segme.frl, tbl_segme_tol, tbl_segme.frr, and
tbl_segme.tor set as integers. So shouldn't the numeric comparisons
work?

I was careful to exclude street names (text) from this query because of
all the headaches involved with finding equal values among 23,000+
unique streets names.

Thank you for taking the time to review my problem! It's definitely
appreciated.

Todd W
 
M

Michel Walsh

Hi,



If your two fields are both numerical, then the *numerical* ordering is
definitively the ordering sequence that will be used.


With Between, three fields are involved. Can you "break" your

x Between y and z

into

x >= y AND x<= z


They may be then easier to detect which comparison "fails", or is not as
expected... which may lead to the resolution of the observed problem.



Hoping it may help,
Vanderghast, Access MVP
 
T

Todd W

Hi, Michel.

The way I see it, I would need 9 unique comparisons to be run.

Since each record in tbl_segme has a frr, tor, frl, and tol, I need to
compare the address number to all four. To simplify, I'll use x as the
address number, a as frl, b as tol, c as frr, and d as tor.

Knowing that x needs to be outside of the valid range, the following
comparisons will tell me if that's true for the left side:
(x < a AND x < b) OR (x < a AND x > b) OR (x > a AND x > b)

and right side:
(x < c AND x < d) OR (x < c AND x > d) OR (x > c AND x > d)

Any other comparison would prove that x is in the valid range.

So I assume that I would need to perform the following comparisons:
(((x<a AND x<b) AND (x<c AND x<d)) OR
((x<a AND x<b) AND (x<c AND x>d)) OR
((x<a AND x<b) AND (x>c AND x>d)) OR
((x<a AND x>b) AND (x<c AND x<d)) OR
((x<a AND x>b) AND (x<c AND x>d)) OR
((x<a AND x>b) AND (x>c AND x>d)) OR
((x>a AND x>b) AND (x<c AND x<d)) OR
((x>a AND x>b) AND (x<c AND x>d)) OR
((x>a AND x>b) AND (x>c AND x>d)))

Seems quite unwieldy, but shouldn't that produce the desired results?

Thanks for letting me bounce this off you. It's always better to have
another set of eyes looking at a problem. I appreciate your help.

Todd
 
M

Michel Walsh

Hi,


I must say I have lost you. I don't remember to have see the description of
"rule" you try to express with the Boolean expression (except through the
Boolean expression you just supply).

Logically, the statement you wrote:

(x < a AND x < b) OR (x < a AND x > b) OR (x > a AND x > b)


is the same as


(x < a AND x < b) OR (x < a AND x > b)
OR
(x < a AND x > b) OR (x > a AND x > b)



since r OR s OR t <==> r OR s OR s OR t


Now, by distributive of OR over AND, we can rewrite the previous statement
as

( (x<a) AND ( x<b OR x>b) )
OR
( (x<a OR x>b ) AND (x>b) )


while it stands that x<>b ( x different than b)
is the same as x<b OR x>b,
(bccause only one can be true among i<j, i=j, i>j )

that leads to:

( x<>b AND x< a) OR ( x<>a AND x > b)

which has one less OR than the original statement. The statement is false if
x=a. It is also false if x=b. Again, it is false if we have both
conditions: x>a and x<b. A weaker form of your statement could thus be:

x<a OR x >b

Are you trying to find not overlapping intervals? Two intervals [a, b], [m,
n] won't overlap, indeed, if and only if

b<m OR a>n


and thus, you can, then, in this case, use that simplified version for your
criteria.




Vanderghast, Access MVP
 
T

Todd W

Well, Michel...I figured out at least why I get undesirable results
using my not between statement.

Using my original example, where there are four entries in tbl_segme
for WINDMILL LN and four customer addresses in tbl_unpub, my query
returns exactly what it should: 4 records that do not fit the ranges!

Let's start with the first tbl_unpub record, 1 WINDMILL LN. As it
turns out, this record does NOT fit any of the ranges and so it should
be in my results. The rest of the records, 23, 154, and 177 WINDMILL
LN, do not fit 3 of the 4 tbl_segme records.

Undesirable results, but not entirely unexpected.

I just need to figure out how to structure the query to do the
following:
1) see if tbl_unpub.addr fits the range for a particular tbl_segme
record
a) if it does, move to next tbl_unpub record
b) if it does not fit, check the next tbl_segme record
i) if no more tbl_segme records, return tbl_unpub.addr as an
invalid range

Todd
 
Top