Query criteria with values and null

[robsworld78]

Hi, I'm stuck trying to make a query and its giving me the run around.

The query is made and I'm trying to add a criteria to a column. Not all the records have a value in the column and I want those to be part of the criteria.

I have this and it works

<>"on road" And <>"weighed" And <>"review"

of course with that criteria the records with no value disappear. If I add "And Is Null" to the above criteria it comes up with no records all together.

If I add just "is null" it shows only the records with no value in the column as it should but for the life of me I can't mix the 2.



So how do I say

<>"on road" And <>"weighed" And <>"review" And Is Null

Thanks

[Bob Fitz]

You could try OR instead of AND:
<>"on road" OR <>"weighed" OR <>"review" OR Is Null

[robsworld78]

Thanks, I tried that already but it returns everything even if the value is "on road" or whatever.

[Bob Fitz]

Can you post the query's SQL

[robsworld78]

This is what I'm using.

SELECT Vendors.VendorID, [Firstname]+" "+[Lastname] AS FullName, Vendors.LastName, VendorsLastStatus.LastOfStatus
FROM Vendors LEFT JOIN VendorsLastStatus ON Vendors.VendorID = VendorsLastStatus.VendorID
GROUP BY Vendors.VendorID, [Firstname]+" "+[Lastname], Vendors.LastName, VendorsLastStatus.LastOfStatus
HAVING (((Vendors.LastName)<>"temp storage") AND ((VendorsLastStatus.LastOfStatus)<>"on road" And (VendorsLastStatus.LastOfStatus)<>"weighed" And (VendorsLastStatus.LastOfStatus)<>"review"))
ORDER BY [Firstname]+" "+[Lastname];

[Bob Fitz]

Try Is Null in the second criteria line, in each of the columns that already have criteria set.

[robsworld78]

If I put Is Null on 2nd line for both columns with criteria it changes nothing, if I only add it to the column I'm having trouble with it shows the correct records however it ignores the criteria on the other column so I get a bunch of records I don't want.

[recyan]

Did you try something like below :

 
GROUP BY 
...............
HAVING 
	(
		(
			(Vendors.LastName)<>"temp storage"
		) 
		AND 
		(
			(VendorsLastStatus.LastOfStatus)<>"on road" 
			And 
			(VendorsLastStatus.LastOfStatus)<>"weighed" 
			And 
			(VendorsLastStatus.LastOfStatus)<>"review" 
			Or 
			(VendorsLastStatus.LastOfStatus) Is Null
		)
	)
ORDER BY 
...............

Thanks

[robsworld78]

Nice, that works, thanks! That put <>"temp storage" on both criteria lines and what Bob said to do one the other criteria.

Thanks for the help!

[recyan]

Just check, what happens if you have Null Values in LastName field in the table.
Then check out below :

SELECT 
	Vendors.VendorID, 
	[Firstname]+" "+Nz([Lastname],"") AS FullName, 
	Vendors.LastName, VendorsLastStatus.LastOfStatus
FROM 
	Vendors 
	LEFT JOIN 
	VendorsLastStatus 
	ON 
	Vendors.VendorID = VendorsLastStatus.VendorID
GROUP BY 
	Vendors.VendorID, 
	[Firstname]+" "+Nz([Lastname],""), 
	Vendors.LastName, 
	VendorsLastStatus.LastOfStatus
HAVING 
	(
		(
			(Vendors.LastName)<>"temp storage"
		) 
		AND 
		(
			(VendorsLastStatus.LastOfStatus)<>"on road" 
			And 
			(VendorsLastStatus.LastOfStatus)<>"weighed" 
			And 
			(VendorsLastStatus.LastOfStatus)<>"review"
		)
	) 
	OR 
	(
		(
			(Vendors.LastName)<>"temp storage"
		) 
		AND 
		(
			(VendorsLastStatus.LastOfStatus) Is Null
		)
	) 
	OR 
	(
		(
			(Vendors.LastName) Is Null
		)
	)
ORDER BY [Firstname]+" "+Nz([Lastname],"");

Thanks

[robsworld78]

That should never happen, the table requires a last name but I changed it to that just in case that changes later. It works flawless. Thanks!!

[recyan]

Glad you found it helpful. I think we should avoid Null Values & instead have default values set everywhere.
The credit is entirely Bob's. I was just translating his thoughts as he was thinking.

Thanks

[robsworld78]

I try hard to avoid the null values but in this case its unavoidable as I'm mixing tables for the query and new vendors don't have an order in the system yet so no status stored anywhere. I tried the Nz thing hoping it will fill it for the query sake but it did nothing, I use that method for lots of number columns. Anyways thanks again and yes Bob was helpful as always.

Cheers

[Bob Fitz]

Glad your problem is sorted. Always happy to help, if I can