CODEWITHSUNDEEP
sqlms-access
Jeffreybo
Jeffreybo

Reputation: 21

How should I eliminate a NULL value tuple in SQL query in ACCESS

I am currently writing a query in ACCESS. However, the result has a row with NULL value. The following is my code:

SELECT 
DISTINCT A.pcode AS [Product Type], 
Sum([footage]*[lb_ft]*12) AS TotalWeightUsed, 
Sum(A.totlength) AS TotalLength, 
Count(A.pcode) AS NumberOfPieces, 
A.date_

FROM A

GROUP BY A.pcode, A.date_
HAVING  (((A.date_)=#4/16/2014#)) and A.pcode IS NOT NULL;

There is a row of A.pcode of value NULL. I have tried "Distinct" and "A.pcode IS NOT NULL" but they cannot eliminate the NULL value row. I am pretty new to Access. I wonder if there is anything I can do.

Upvotes: 0

Views: 87

Answers (1)

Johnny Bones
Johnny Bones

Reputation: 8404

I'm going to guess that this should work. You shouldn't need the DISTINCT qualifier:

SELECT 
  A.pcode AS [Product Type], 
  Sum([footage]*[lb_ft]*12) AS TotalWeightUsed, 
  Sum(A.totlength) AS TotalLength, 
  Count(A.pcode) AS NumberOfPieces, 
  A.date_
FROM A
WHERE 
  A.date_ = #4/16/2014# 
  AND A.pcode IS NOT NULL
GROUP BY 
  A.pcode, 
  A.date_;

Upvotes: 1

Related Questions