Null Values Still Showing Up

[SpongebobKP]

OK, I'm kind of a novice so this may be a simple dilemma. The issue is that I had originally made one query to exclude Null values from one field, DELIVERABILITY_CODE:



SELECT current_addresses.* INTO CHANGE_OF_ADDRESS
FROM current_addresses
WHERE (Nz(current_addresses.DELIVERABILITY_CODE,"")="");

That went fine, and so I made another query to show a selection of possible values in another field, MOVE_TYPE:

SELECT current_addresses.* INTO CHANGE_OF_ADDRESS
FROM current_addresses
WHERE (((current_addresses.MOVE_TYPE)="B")) OR (((current_addresses.MOVE_TYPE)="I"));

That also went fine as well.

However, I'm now trying to combine the WHERE conditional to show the MOVE_TYPE values ONLY when the DELIVERABILITY_CODE is Null. I can't seem to combine that in a way that excludes the Null values on the DELIVERABILITY_CODE. I've tried using:

WHERE (Nz(current_addresses.DELIVERABILITY_CODE,"")="") AND (((current_addresses.MOVE_TYPE)="F")) OR (((current_addresses.MOVE_TYPE)="B")) OR (((current_addresses.MOVE_TYPE)="I"));

and also

WHERE (Nz(current_addresses.DELIVERABILITY_CODE,"")="") OR (((current_addresses.MOVE_TYPE)="F")) OR (((current_addresses.MOVE_TYPE)="B")) OR (((current_addresses.MOVE_TYPE)="I"));

but to no avail. I'm not sure where I need to make changes to get what I'm looking for.

[CJ_London]

your nz function may be giving you the wrong result, try

WHERE current_addresses.DELIVERABILITY_CODE is null AND current_addresses.MOVE_TYPE in ("F","B","I")

[June7]

Have to be very careful in mixing AND and OR operators. Parentheses are critical.


Maybe only need:

WHERE current_addresses.DELIVERABILITY_CODE IS NULL


There are only 3 MOVE_TYPE values or you are interested in only those 3? Will MOVE_TYPE always have data or could there be Null? Do you want to exclude records where MOVE_TYPE Is Null?

Maybe:

WHERE current_addresses.DELIVERABILITY_CODE IS NULL AND NOT current_addresses.MOVE_TYPE IS NULL

[SpongebobKP]

That actually works perfectly with AND NOT. The (F,B,I) are the only values that will ever show up in MOVE_TYPE and the rest would be Null so this filters out all those I am looking for. Thanks!