query requires value for a field in order to display results

  • Thread starter Thread starter Susan
  • Start date Start date
S

Susan

The following SQL code is generated by my Access query. In order for results
to display for the CustomTex19 field, the field must contain a value. This
was not the case prior to me changing the query slightly. How do I change
this query so that null values in this field will not prevent the query from
returning rows:

SELECT DISTINCTROW DocumentHeaders.ID, DocumentHeaders.DocStatus,
DocumentHeaders.CustomText10, DocumentHeaders.CustomText08,
DocumentHeaders.CustomText19, DocumentHeaders.ShipToCompany,
DocumentItems.DocID, DocumentHeaders.SoldToContact,
DocumentHeaders.ShipToAddress1, DocumentHeaders.ShipToAddress2,
DocumentHeaders.ShipToCity, DocumentHeaders.ShipToState,
DocumentHeaders.ShipToPostalCode, DocumentHeaders.ShipToCountry,
DocumentHeaders.SoldToPhone, DocumentHeaders.SoldToPhoneExt,
DocumentHeaders.SoldToCMAccountNo, Products.ManufacturerPartNumber,
productfamily_fam.prodfamid_fam, Zips.*, DocumentHeaders.CustomText12,
DocumentHeaders.DocDate, DocumentHeaders.CustomText04
FROM (productfamily_fam RIGHT JOIN Products ON
productfamily_fam.prodfamid_fam = Products.ProductFamily) INNER JOIN (Zips
INNER JOIN (DocumentHeaders INNER JOIN DocumentItems ON DocumentHeaders.ID =
DocumentItems.DocID) ON Zips.ZIP_CODE = DocumentHeaders.ShipToPostalCode) ON
Products.ManufacturerPartNumber = DocumentItems.ManufacturerPartNumber
WHERE (((DocumentHeaders.DocStatus)="invoice") AND
((DocumentHeaders.CustomText08)<>"True") AND
((DocumentHeaders.CustomText19)<>"Reseller" And
(DocumentHeaders.CustomText19)<>"Supplier") AND
((DocumentHeaders.ShipToCompany)<>"") AND
((Products.ManufacturerPartNumber)<>""))
ORDER BY DocumentHeaders.DocDate DESC;
 
Change the where clause. Removing all those extraneous parentheses that
Access seems to love. I think you need the following.

WHERE DocumentHeaders.DocStatus="invoice"
AND DocumentHeaders.CustomText08<>"True"
AND
(DocumentHeaders.CustomText19<>"Reseller"
And DocumentHeaders.CustomText19<>"Supplier"
OR DocumentHeaders.CustomText19 is null)

AND DocumentHeaders.ShipToCompany<> ""
AND Products.ManufacturerPartNumber<> ""


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top