Query to include null values

[CW]

I have a query that pulls quite a lot of data for each of our shipment
records, including the invoice number. However, I'm finding that where a
shipment hasn't been invoiced, that whole record is being omitted from the
results.
In those cases I do want the records to be shown, but there should be a
blank for the invoice number.
I guess this may involve the use of Nz and have tried several ways but I'm
not sure of the correct syntax or even if that is the right solution...
Many thanks
CW

 

[Bob Quintal]

I have a query that pulls quite a lot of data for each of our
shipment records, including the invoice number. However, I'm
finding that where a shipment hasn't been invoiced, that whole
record is being omitted from the results.
In those cases I do want the records to be shown, but there should
be a blank for the invoice number.
I guess this may involve the use of Nz and have tried several ways
but I'm not sure of the correct syntax or even if that is the
right solution... Many thanks
CW

I will assume that the query joins the shipments table to the invoices
table. You will need to change the join type _/1 from an inner join to
a left join, and make sure that there are no critera _/2 other than the
join against fields in the invoices table.

_/1: double click on the join line in the query builder and select the
applicable option (include all records from shipments and only....)

_/2 any mandatory criteria in the invoices table can be prefixed with
IS NULLL OR in the criteria field.

 

[CW]

I have tried altering the join type but unfortunately this made no difference
- other suggestions, please?
Many thanks
CW

 

[Bob Quintal]

I have tried altering the join type but unfortunately this made no
difference - other suggestions, please?
Many thanks
CW

You'll have to post the SQL of the query.

 

[CW]

OK, here it is:
Many thanks
CW

SELECT Inquiries.Ref, Inquiries.Customer1Surname AS Client,
Inquiries.InquiryDate, Inquiries.SurveyDate AS [Survey Date],
Inquiries.OriginAddr03 AS [Orig Town], Inquiries.OriginCountry AS [Orig
Ctry], Inquiries.DestAddr03 AS [Dest Town], Inquiries.DestCountry AS [Dest
Ctry], Inquiries.Status, Inquiries.LostReason, Events.BookedDate AS [Booked
Date], Inquiries.CommissionPaid AS [Comm Paid?], Inquiries.InqStatus,
qryInvoicesForJob.InvNo, qryInvoicesForJob.Paid

FROM (Inquiries LEFT JOIN Events ON Inquiries.Ref = Events.Ref) LEFT JOIN
qryInvoicesForJob ON Inquiries.Ref = qryInvoicesForJob.Ref

WHERE (((Inquiries.Surveyor)="Herdeep Chaggar") AND
((qryInvoicesForJob.InvType)<>"Credit Note") AND
((qryInvoicesForJob.InvStatus)<>"Cancelled"))

ORDER BY Inquiries.InquiryDate DESC;

 

[Bob Quintal]

WHERE (((Inquiries.Surveyor)="Herdeep Chaggar") AND
((qryInvoicesForJob.InvType)<>"Credit Note") AND
((qryInvoicesForJob.InvStatus)<>"Cancelled"))

WHERE (((Inquiries.Surveyor)="Herdeep Chaggar") AND
((qryInvoicesForJob.InvType is Null OR qryInvoicesForJob.InvType)
<>"Credit Note") AND
((qryInvoicesForJob.InvStatus is Null or qryInvoicesForJob.InvStatus)

 

[Bob Quintal]

WHERE (((Inquiries.Surveyor)="Herdeep Chaggar") AND
((qryInvoicesForJob.InvType is Null OR qryInvoicesForJob.InvType)
<>"Credit Note") AND
((qryInvoicesForJob.InvStatus is Null or

I think I messed up some parentheses. Access uses too many.
WHERE (Inquiries.Surveyor="Herdeep Chaggar") AND
(qryInvoicesForJob.InvType is Null OR qryInvoicesForJob.InvType
<>"Credit Note") AND
(qryInvoicesForJob.InvStatus is Null or qryInvoicesForJob.InvStatus
<>"Cancelled")

 

[John Spencer]

Since you are applying criteria in the where clause to qryInvoicesForJob
the LEFT JOIN is being overridden by the criteria in the WHERE clause.

You probably need to apply the criteria for InvType and InvStatus in the
qryInvoicesForJob to handle this.

A second option that might work would be to try what Bob Quintal
suggests. The problem with that is that if qryInvoicesForJob returns
any records even if they don't meet the criteria specified in the where
clause, then a null value will not be returned in most cases.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

OK, here it is:
Many thanks
CW

SELECT Inquiries.Ref, Inquiries.Customer1Surname AS Client,
Inquiries.InquiryDate, Inquiries.SurveyDate AS [Survey Date],
Inquiries.OriginAddr03 AS [Orig Town], Inquiries.OriginCountry AS [Orig
Ctry], Inquiries.DestAddr03 AS [Dest Town], Inquiries.DestCountry AS [Dest
Ctry], Inquiries.Status, Inquiries.LostReason, Events.BookedDate AS [Booked
Date], Inquiries.CommissionPaid AS [Comm Paid?], Inquiries.InqStatus,
qryInvoicesForJob.InvNo, qryInvoicesForJob.Paid

FROM (Inquiries LEFT JOIN Events ON Inquiries.Ref = Events.Ref) LEFT JOIN
qryInvoicesForJob ON Inquiries.Ref = qryInvoicesForJob.Ref

WHERE (((Inquiries.Surveyor)="Herdeep Chaggar") AND
((qryInvoicesForJob.InvType)<>"Credit Note") AND
((qryInvoicesForJob.InvStatus)<>"Cancelled"))

ORDER BY Inquiries.InquiryDate DESC;

You'll have to post the SQL of the query.

 

[CW]

Thanks, John. I think I'll re-write and simplify my queries a bit, and will
change the criteria as per your suggestion
Thanks again
CW

Since you are applying criteria in the where clause to qryInvoicesForJob
the LEFT JOIN is being overridden by the criteria in the WHERE clause.

You probably need to apply the criteria for InvType and InvStatus in the
qryInvoicesForJob to handle this.

A second option that might work would be to try what Bob Quintal
suggests. The problem with that is that if qryInvoicesForJob returns
any records even if they don't meet the criteria specified in the where
clause, then a null value will not be returned in most cases.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

OK, here it is:
Many thanks
CW

SELECT Inquiries.Ref, Inquiries.Customer1Surname AS Client,
Inquiries.InquiryDate, Inquiries.SurveyDate AS [Survey Date],
Inquiries.OriginAddr03 AS [Orig Town], Inquiries.OriginCountry AS [Orig
Ctry], Inquiries.DestAddr03 AS [Dest Town], Inquiries.DestCountry AS [Dest
Ctry], Inquiries.Status, Inquiries.LostReason, Events.BookedDate AS [Booked
Date], Inquiries.CommissionPaid AS [Comm Paid?], Inquiries.InqStatus,
qryInvoicesForJob.InvNo, qryInvoicesForJob.Paid

FROM (Inquiries LEFT JOIN Events ON Inquiries.Ref = Events.Ref) LEFT JOIN
qryInvoicesForJob ON Inquiries.Ref = qryInvoicesForJob.Ref

WHERE (((Inquiries.Surveyor)="Herdeep Chaggar") AND
((qryInvoicesForJob.InvType)<>"Credit Note") AND
((qryInvoicesForJob.InvStatus)<>"Cancelled"))

ORDER BY Inquiries.InquiryDate DESC;

I have tried altering the join type but unfortunately this made no
difference - other suggestions, please?
Many thanks
CW

You'll have to post the SQL of the query.

:


I have a query that pulls quite a lot of data for each of our
shipment records, including the invoice number. However, I'm
finding that where a shipment hasn't been invoiced, that whole
record is being omitted from the results.
In those cases I do want the records to be shown, but there
should be a blank for the invoice number.
I guess this may involve the use of Nz and have tried several
ways but I'm not sure of the correct syntax or even if that is
the right solution... Many thanks
CW

I will assume that the query joins the shipments table to the
invoices table. You will need to change the join type _/1 from an
inner join to a left join, and make sure that there are no
critera _/2 other than the join against fields in the invoices
table.

_/1: double click on the join line in the query builder and
select the applicable option (include all records from shipments
and only....)

_/2 any mandatory criteria in the invoices table can be prefixed
with IS NULLL OR in the criteria field.